總結(jié)一下Excel SQL查詢中"表"的技巧
發(fā)布時間:2022-11-02 10:01 [ 我要自學(xué)網(wǎng)原創(chuàng) ] 發(fā)布人: 小劉2175

今天聊一下SQL語句中的Excel表。

1.區(qū)域成表

Excel工作表和數(shù)據(jù)庫的數(shù)據(jù)表有很多不同之處,最顯著的地方在于,數(shù)據(jù)庫的數(shù)據(jù)表可以理解為由行列構(gòu)成,而Excel工作表則是由一個又一個單元格構(gòu)成,且這些單元格擁有獨(dú)特的地址表述方法,也就是A1或R1C1,它們還可以構(gòu)成數(shù)據(jù)相連的單元格區(qū)域,例如A2:H8。
那么問題來了,如果我們只需要計算某張Excel工作表的部分區(qū)域的話,SQL該怎么表述呢?
這種問題是很常見的。
比如,很多人的Excel標(biāo)題行并不是處于表格的第一行,而是第2行……
如下圖所示▼



此時,我們希望計算A2:F列的單元格區(qū)域,這樣我們更容易使用字段名處理數(shù)據(jù),而不是整張Excel工作表……
再比如,一張表里存在兩個或更多個“表”……這句話什么意思呢?
見下圖▼



圖中所示的表格中,既存在一份“教師表”,又存在一份“學(xué)生表”;如果我們只希望SQL引用計算A2:D8的教師表數(shù)據(jù)……
……Excel中的SQL其實(shí)是支持將工作表的單元格區(qū)域作為“表”使用的。
上圖所示的問題,SQL可以寫成:
SELECT 姓名,學(xué)科 FROM [數(shù)據(jù)表$A2:D8]
查詢結(jié)果如下:



而第1種情況,我們知道數(shù)據(jù)開始于A2單元格,但不知道結(jié)束于F列的哪個單元格,SQL可以寫成:
SELECT 姓名,愛好 FROM [學(xué)生表$A2:F]
另外,如果我們需要SQL引用計算表格D:G整列的數(shù)據(jù),SQL可以寫成:
SELECT * FROM [學(xué)生表$D:G]
總結(jié)以上幾種Excel工作表區(qū)域的表述方式,也就是,工作表名稱+美金符號$+相對引用狀態(tài)下的單元格地址,最后使用中括號包起來。
就醬紫。

?本節(jié)小貼士:
[學(xué)生表$A2:F],我們說該語句可以引用從A2至F列最后存在數(shù)據(jù)的單元格區(qū)域,但這是有一個限制前提條件的,即非自連接狀態(tài)。所謂自連接是指SQL應(yīng)用于鏈接自身的工作簿。自鏈接狀態(tài)下,A2:F的表達(dá)方式最多是A2:F65536行;倘若此時需要的引用行超過65536行,請使用整表模式。

 

2.跨工作簿的表

一個眾所周知的問題是,Excel函數(shù)在處理跨工作薄數(shù)據(jù)時很是疲態(tài),除了個別幾個查找引用類函數(shù)(例如VLOOKUP等),絕大部分函數(shù)都需要打開相關(guān)工作簿后才可以計算使用。
是的,VLOOKUP函數(shù)并不需要打開相關(guān)工作簿也可以跨工作薄使用,而且在VLOOKUP公式書寫完成后,即便你把它所引用的工作簿給刪了,也不妨礙它計算,這是因?yàn)樗呀?jīng)把相關(guān)數(shù)據(jù)緩存到了公式所在的工作簿中,不過VLOOKUP這種模式并不支持函數(shù)復(fù)雜嵌套……打個響指,關(guān)于這一點(diǎn),如果你感興趣,我們改天單獨(dú)聊一下。

……咳,說回SQL~~
……我們之前分享的SQL語句都是處理當(dāng)前工作簿的表格,如果我們所需要處理的數(shù)據(jù)位于其它工作簿時,SQL該怎么表述呢?
例如,獲取位于計算機(jī)D盤的“EH小學(xué)”文件夾下的“學(xué)生表.xlsx”工作簿中的“成績表”的所有數(shù)據(jù)——一口氣讀完這話的,不得不讓在下心生佩服。
如果是OLE DB法(該方法參考本系列教程第1章),SQL語句如下▼
SELECT * FROM [D:\EH小學(xué)\學(xué)生表.xlsx].[成績表$]
FROM后指定表字符串有兩個部分構(gòu)成,第一個中括號內(nèi)是指定工作簿的存放路徑+帶后綴的完整工作簿名稱,后一個中括號內(nèi)是工作表名稱,兩個中括號之間使用英文點(diǎn)號(.)相連。
如果是通過VBA+ADO使用SQL語句……
敲書柜前方預(yù)警:VBA基礎(chǔ)差的童鞋請自行跳過以下內(nèi)容……
相比于OLE DB法,VBA+ADO的方法要靈活的多,它可以使用ADO直接創(chuàng)建并打開與指定工作簿的鏈接,因此SQL語句就無需再指定工作簿完整名稱等。

代碼參考如下▼

Sub ADO_SQL()
'適用于除2003版以外的高版本Excel
Dim cnn As Object, rst As Object
Dim strPath As String, strCnn As String, strSQL As String
Dim i As Long
Set cnn = CreateObject("adodb.connection")
strPath = "D:\EH小學(xué)\學(xué)生表.xlsx" '指定工作簿
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn '創(chuàng)建并打開到指定工作簿的鏈接
strSQL = "SELECT * FROM [成績表$]" 'strSQL語句,查詢成績表的所有數(shù)據(jù)
Set rst = cnn.Execute(strSQL) '執(zhí)行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 = Nothing
End Su

以上代碼第7行直接指定了需要連接的工作簿完整名稱,SQL語句內(nèi)也就不再需要特別處理。

但更多的情況是,ADO創(chuàng)建的鏈接是一個工作簿,需要獲取的數(shù)據(jù)在另一個或多個工作簿,例如兩個工作簿之間的數(shù)據(jù)查詢統(tǒng)計。此時通常使用的代碼如下▼

Sub ADO_SQL2()
'適用于除2003版以外的高版本Excel
Dim cnn As Object, rst As Object
Dim strPath As String, strCnn As String, strSQL As String
Dim i As Long
Set cnn = CreateObject("adodb.connection")
strPath = ThisWorkbook.FullName '代碼所在工作簿的完整名稱
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
cnn.Open strCnn '創(chuàng)建到代碼所在工作簿的鏈接
strSQL = "SELECT * FROM [Excel 12.0;DATABASE=D:\EH小學(xué)\學(xué)生表.xlsm].[成績表$]"
Set rst = cnn.Execute(strSQL) '執(zhí)行SQL
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 = Nothing
End Sub

代碼中第7行創(chuàng)建了當(dāng)前工作簿的鏈接,SQL語句中又指定了另外一個工作簿的鏈接。SQL語句如下▼
SELECT * FROM [Excel 12.0;DATABASE=D:\EH小學(xué)\學(xué)生表.xlsx].[成績表$]

FROM指定表的字符串有兩部分組成。第一個中括號中,Excel 12.0是目標(biāo)工作簿的版本號,第2章時我們講過,Excel 12.0適用于除了2003以外的所有Excel版本。DATABASE指定的是數(shù)據(jù)源工作簿的路徑和名稱。第2個中括號內(nèi)是工作表名。兩個中括號之間使用英文點(diǎn)號相連。
……
看起來似乎VBA+ADO方法的SQL語句比OLE DB法更復(fù)雜?確實(shí)如此,不過前者的功能也更強(qiáng)大。比如,它可以通過VBA對象的屬性、方法,循環(huán)和判斷語句等,有條件的篩選工作簿和工作表……相比之下,OLE DB中的SQL語句就是純手工常量模式了。當(dāng)然,更重要的是,前者不但可以查數(shù)據(jù),還可以增改刪數(shù)據(jù),后者卻只限于查。

Excel2019視頻教程
我要自學(xué)網(wǎng)商城 ¥80 元
進(jìn)入購買
文章評論
0 條評論 按熱度排序 按時間排序 /350
添加表情
遵守中華人民共和國的各項(xiàng)道德法規(guī),
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任,
本站有權(quán)保留或刪除有爭議評論。
參與本評論即表明您已經(jīng)閱讀并接受
上述條款。
V
特惠充值
聯(lián)系客服
APP下載
官方微信
返回頂部
分類選擇:
電腦辦公 平面設(shè)計 室內(nèi)設(shè)計 室外設(shè)計 機(jī)械設(shè)計 工業(yè)自動化 影視動畫 程序開發(fā) 網(wǎng)頁設(shè)計 會計課程 興趣成長 AIGC