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

在我的系列書籍中一直在強(qiáng)調(diào)“搭積木”的編程思路,，主要的內(nèi)涵：首先是代碼不要自己全部的錄入,，你要做的是把積木放在合適的位置讓后去修正代碼，其次是建立自己的“積木庫(kù)”,把自己認(rèn)為有用的代碼放在一起,，可以隨時(shí)利用,。你的積木庫(kù)資料越多，你做程序的思路就會(huì)越多,。數(shù)據(jù)庫(kù)的代碼錄入更是如此,，代碼往往很長(zhǎng)，千萬不要自己去錄入,?？崭瘢?hào),，逗號(hào)的寫法要求是非常嚴(yán)格的,。一定要拷貝，然后修正代碼,，把時(shí)間利用到高效的思考上,。

1  應(yīng)用場(chǎng)景的具體分析

今日的內(nèi)容是講聚合函數(shù)和SQL的結(jié)合,，從而完成我們的實(shí)際工作。如下的實(shí)例：我的工作表中有兩頁(yè)格式接近的數(shù)據(jù),，如下：

現(xiàn)在我要把兩個(gè)工作表的數(shù)據(jù)提取型號(hào),，數(shù)量，單價(jià),，并把數(shù)量按型號(hào)匯總,，匯總后的數(shù)據(jù)按型號(hào)排序處理。

上面的例子,，如果在EXCEL工作表文件中處理要多個(gè)步驟來完成,，用數(shù)據(jù)庫(kù)的一般方案也是比較麻煩的，下面看我們聯(lián)合函數(shù)的功效吧,。

2  完成多工作表的匯總查詢計(jì)算的代碼及代碼解讀

我給出的代碼如下;

Sub mynzRecords_51() '第51講  利用聯(lián)合函數(shù)和SQL語句完成多工作表的匯總查詢計(jì)算

 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")

     '建立一個(gè)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 型號(hào),數(shù)量,單價(jià) from [數(shù)據(jù)$]"

     strSQL2 = "select 型號(hào),數(shù)量,單價(jià) from [數(shù)據(jù)2$]"

     strSQL3 = strSQL1 & " UNION ALL " & strSQL2

     strSQL4 = "select 型號(hào),SUM(數(shù)量),單價(jià) from (" & strSQL3 & ") GROUP BY 型號(hào),單價(jià)"

      arr = Array("型號(hào)", "數(shù)量", "單價(jià)")

     [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 型號(hào),數(shù)量,單價(jià) from [數(shù)據(jù)$]" 第一個(gè)SQL語句完成“數(shù)據(jù)”工作表的數(shù)據(jù)提取,。

  2  strSQL2 = "select 型號(hào),數(shù)量,單價(jià) from [數(shù)據(jù)2$]" 第二個(gè)SQL語句完成“數(shù)據(jù)2”工作表的數(shù)據(jù)提取。

  3  strSQL3 = strSQL1 & " UNION ALL " & strSQL2 第三個(gè)SQL語句完成strSQL1和strSQL2的組合,，這里利用到了聯(lián)合函數(shù)UNION,，此函數(shù)的用法大家要注意,，按照我給出的代碼示例進(jìn)行即可,。

  4  strSQL4 = "select 型號(hào),SUM(數(shù)量),單價(jià) from (" & strSQL3 & ") GROUP BY 型號(hào),單價(jià)"

第四個(gè)SQL語句，通過上面的聚合函數(shù)建立一個(gè)新的SQL查詢,。同樣這個(gè)語句的寫法要注意按照我的示例格式進(jìn)行,，不要另辟蹊徑，通不過的,。

下面看我們代碼的運(yùn)行結(jié)果：

今日內(nèi)容回向：

1  聚合函數(shù)在sql中如何應(yīng)用,？

2  如何利用聯(lián)合函數(shù)完成數(shù)據(jù)的統(tǒng)計(jì)？