Excel VBA ADO SQL入門教程012:多表數(shù)據(jù)合并匯總

asaser 2022-07-24 發(fā)布于四川

展開全文

今天給大家分享一下如何使用SQL合并多工作表數(shù)據(jù)。

在SQL中，UNION運(yùn)算符可以合并兩個(gè)或多個(gè)的SELECT語句的查詢結(jié)果，因此，我們通常使用它來合并多表數(shù)據(jù)。

例如，在一個(gè)工作簿中存在兩個(gè)表，一個(gè)表名為 '一班'，另一個(gè)表名為'二班'，現(xiàn)在我們需要將兩個(gè)表的數(shù)據(jù)合并成一張表。

SQL語句如下：

SELECT * FROM [一班$] UNION SELECT * FROM [二班$]

結(jié)果如下：

需要說明的是，UNION會(huì)對(duì)合并的結(jié)果去重復(fù)，只保留唯一值；前面講去重復(fù)的時(shí)候我們提過，SQL去重復(fù)的過程，是先對(duì)記錄排序，然后再去重復(fù)，因此UNION的運(yùn)算結(jié)果是升序排列的不重復(fù)記錄。

依然以上圖所示的數(shù)據(jù)為例，匯總一班和二班兩個(gè)班級(jí)的學(xué)生名單：

SELECT 姓名  FROM [一班$] UNION SELECT 姓名  FROM [二班$]

結(jié)果如下：

由于使用了UNION運(yùn)算符，一、二班都存在的'看見星光'，只保留了一個(gè)。

如果不需要去重復(fù)的操作，可以使用關(guān)鍵字ALL，也就是UNION ALL。

同樣匯總一班和二班兩個(gè)班級(jí)的學(xué)生名單：

SELECT 姓名 FROM [一班$] UNION ALL SELECT 姓名 FROM [二班$]

結(jié)果如下：

使用了關(guān)鍵字ALL后，一、二班存在的'看見星光'，都被保留了下來；而且姓名的排放順序是和出現(xiàn)的順序一致的，并沒有進(jìn)行排序處理。

……

需要說明的是，不管是UNION還是UNION ALL，都要求SELECT語句擁有相同的列數(shù)，而且字段的排放順序必須相同。

當(dāng)列數(shù)不相同時(shí)，例如以下語句：

代碼看不全可以左右拖動(dòng)...▼

SELECT 姓名,語文  FROM [一班$] UNION ALL SELECT 姓名  FROM [二班$]

會(huì)得到錯(cuò)誤提示：

而當(dāng)列的個(gè)數(shù)相同，但順序不相同時(shí)，例如以下語句：

代碼看不全可以左右拖動(dòng)...▼

SELECT 姓名,語文 FROM [一班$] UNION ALL SELECT 語文,姓名 FROM [二班$]

會(huì)得出錯(cuò)誤的結(jié)果。

之所以語句能夠運(yùn)算，但結(jié)果不盡人意（姓名列出現(xiàn)了語文的成績），是因?yàn)閁NION運(yùn)算符總是按第一個(gè)SELECT語句中字段的排放順序處理數(shù)據(jù)的。

第一個(gè)SELECT指定了'姓名'字段第1列、'語文'字段第2列，則默認(rèn)以后的SELECT語句第1列均為'姓名'字段，第2列均為'語文'。

此外，UNION運(yùn)算符總是將第一個(gè)SELECT語句提供的字段名稱作為最終查詢結(jié)果的字段名稱。

例如，以下語句只是在第一個(gè)SELECT子句中使用了別名，但查詢結(jié)果中的字段名依然是按照指定別名呈現(xiàn)的。

代碼看不全可以左右拖動(dòng)...▼

SELECT 姓名,語文 AS 語文成績  FROM [一班$] UNION ALL SELECT 姓名,語文  FROM [二班$]

……

在實(shí)際匯總多表數(shù)據(jù)的過程中，難免會(huì)碰到表格列數(shù)不一致的情況。

例如，以下兩個(gè)表，名字為'一班'的表字段由姓名、語文、數(shù)學(xué)和英語構(gòu)成，而另外一個(gè)名為’二班'的表字段只有姓名、語文和英語，并沒有數(shù)學(xué)字段。如果此時(shí)我們進(jìn)行兩表數(shù)據(jù)匯總，應(yīng)該怎么處理呢？

對(duì)于缺少的字段可以使用某個(gè)值代替。

通常是使用NULL代替，語句如下：

代碼看不全可以左右拖動(dòng)...▼

SELECT 姓名,語文,數(shù)學(xué),英語 FROM [一班$] UNION ALL SELECT 姓名,語文,NULL,英語 FROM [二班$]

結(jié)果如下：

當(dāng)然，也可以使用其它值代替，例如'未考’，語句如下：

SELECT 姓名,語文,數(shù)學(xué),英語  FROM [一班$] UNION ALL SELECT 姓名,語文,'未考',英語  FROM [二班$]

結(jié)果如下：

……

當(dāng)需要匯總的表格列數(shù)統(tǒng)一，但個(gè)數(shù)過多時(shí)，手工輸入SQL語句未免不夠靈活方便，此時(shí)最好是使用VBA的方式。

以本文的第1張圖數(shù)據(jù)為例，使用VBA+ADO+SQL后的代碼如下所示▼

Sub SQL_UNION() Dim cnn As Object, rst As Object Dim strPath As String, str_cnn As String Dim strSQL As String, strTemp As String Dim sht As Worksheet, strShtName As String Dim i As Long Set cnn = CreateObject('adodb.connection') strPath = ThisWorkbook.FullName If Application.Version < 12 Then str_cnn = 'Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=' & strPath Else str_cnn = 'Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=' & strPath End If cnn.Open str_cnn strTemp = 'SELECT 姓名,語文,數(shù)學(xué),英語,' For Each sht In Worksheets strShtName = sht.Name If strShtName <> ActiveSheet.Name Then strSQL = strSQL & strTemp & ''' & strShtName & '' AS 班級(jí) FROM [' & strShtName & '$] UNION ALL ' End If Next strSQL = Left(strSQL, Len(strSQL) - 11) Set rst = cnn.Execute(strSQL) Cells.ClearContents For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1) = rst.Fields(i).Name Next Range('a2').CopyFromRecordset rst cnn.Close Set cnn = NothingEnd Sub

運(yùn)算結(jié)果如下：

代碼中，變量strTemp指定了需要匯總的字段名稱和順序；之后遍歷工作表對(duì)象，合并多個(gè)SELECT語句，并將工作表名稱作為一個(gè)字段補(bǔ)充到SELECT子句中，最后使用ADO執(zhí)行SQL語言獲得查詢結(jié)果。

而當(dāng)需要匯總的表格標(biāo)題名稱統(tǒng)一，但列數(shù)并不統(tǒng)一，表格個(gè)數(shù)又過多時(shí)，可以借助字典先對(duì)列標(biāo)題和排列順序做一個(gè)過濾儲(chǔ)存，然后再編寫和執(zhí)行SQL語句……這個(gè)問題我們就放到介紹記錄集對(duì)象時(shí)再談。

小貼士：

您可能見過這樣的多表合并SQL語句，使用通配符*代替字段名的描述：

SELECT * FROM  [一班$] UNION ALL SELECT * FROM [二班$]

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： asaser > 《Excel VBA ADO SQL入門教程》

舉報(bào)/認(rèn)領(lǐng)