'如何在多個表格中利用聯合函數完成大數據查詢'

大家好，我們繼續VBA數據庫解決方案的學習，今天講解第51講：利用聚合函數和SQL語句完成多工作表的彙總查詢計算。今日的內容看似簡單，其實有些難度，希望大家不要放棄，在自己測試的時候要多測試幾次。雖然本講的內容可利用其他的方法也可以實現，但這種方法也不失為一種解決問題的有效手段。

在我的系列書籍中一直在強調"搭積木"的編程思路，主要的內涵：首先是代碼不要自己全部的錄入，你要做的是把積木放在合適的位置讓後去修正代碼 ，其次是建立自己的"積木庫",把自己認為有用的代碼放在一起，可以隨時利用。你的積木庫資料越多，你做程序的思路就會越多。數據庫的代碼錄入更是如此，代碼往往很長，千萬不要自己去錄入。空格，引號，逗號的寫法要求是非常嚴格的。一定要拷貝，然後修正代碼，把時間利用到高效的思考上。

今日的內容是講聚合函數和SQL的結合，從而完成我們的實際工作。如下的實例：我的工作表中有兩頁格式接近的數據，如下：

現在我要把兩個工作表的數據提取型號，數量，單價，並把數量按型號彙總，彙總後的數據按型號排序處理。

上面的例子，如果在EXCEL工作表文件中處理要多個步驟來完成，用數據庫的一般方案也是比較麻煩的，下面看我們聚合函數的功效吧。

我給出的代碼如下;

Sub mynzRecords_51() '第51講 利用聯合函數和SQL語句完成多工作表的彙總查詢計算

Dim cnADO, rsADO As Object

Dim strPath, strSQL1, strSQL2, strSQL3, strSQL4 As String

Worksheets("51").Select

Cells.ClearContents

Set cnADO = CreateObject("ADODB.Connection")

Set rsADO = CreateObject("ADODB.Recordset")

'建立一個ADO的連接

strPath = ThisWorkbook.FullName

cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;hdr=yes;imex=1';data source=" & strPath

strSQL1 = "select 型號,數量,單價 from [數據$]"

strSQL2 = "select 型號,數量,單價 from [數據2$]"

strSQL3 = strSQL1 & " UNION ALL " & strSQL2

strSQL4 = "select 型號,SUM(數量),單價 from (" & strSQL3 & ") GROUP BY 型號,單價"

arr = Array("型號", "數量", "單價")

[a1:c1] = arr

[a65536].End(xlUp).Offset(1, 0).CopyFromRecordset cnADO.Execute(strSQL4)

cnADO.Close

Set cnADO = Nothing

Set rsADO = Nothing

End Sub

代碼截圖：

代碼講解：

1 strSQL1 = "select 型號,數量,單價 from [數據$]" 第一個SQL語句完成"數據"工作表的數據提取。

2 strSQL2 = "select 型號,數量,單價 from [數據2$]" 第二個SQL語句完成"數據2"工作表的數據提取。

3 strSQL3 = strSQL1 & " UNION ALL " & strSQL2 第三個SQL語句完成strSQL1和strSQL2的組合，這裡利用到了聚合函數UNION，此函數的用法大家要注意，按照我給出的代碼示例進行即可。

4 strSQL4 = "select 型號,SUM(數量),單價 from (" & strSQL3 & ") GROUP BY 型號,單價"

第四個SQL語句，通過上面的聚合函數建立一個新的SQL查詢。同樣這個語句的寫法要注意按照我的示例格式進行，不要另闢蹊徑，通不過的。

下面看我們代碼的運行結果：

今日內容迴向：

1 聚合函數在sql中如何應用？

2 如何利用聚合函數完成數據的統計？