SQL入門教程第04課：SQL查詢中"表"技巧的總結(jié)
原創(chuàng)
EH看見星光
Excel星球
Excel星球
微信號
AhaExcel
功能介紹
建議常用Excel的職場人關(guān)注，海量教程隨學(xué)隨用，隨用隨查。 主創(chuàng)：看見星光，微軟全球最有價(jià)值專家、Excel圖書作者、培訓(xùn)師。 內(nèi)容：每日四文，一篇函數(shù)教程、一篇VBA教程、一個(gè)短視頻小技巧、一篇雜文。
微博：EXCELers / 知識(shí)星球：Excel
HI，大家好，我是星光。上期我們聊了SQL常用查詢語句中的字段查詢，其簡化版語法如下：
當(dāng)場我就……小聲說了，關(guān)鍵字F
ROM指明了要獲取字段信息的表名。倘若數(shù)據(jù)源是Excel表格，則需要在表名后增加美元符號$，并用中括號包起來，例如[Sheet1$]……
事實(shí)上，上述例子是SQL In Excel 對工作表引用最簡單的一種情況，也就是
整表引用
；此外還有單元格區(qū)域引用、跨工作簿引用等。
所以咱們今天就再來集中聊一下SQL語句中的Excel表。
1.區(qū)域成表
Excel工作表和數(shù)據(jù)庫的數(shù)據(jù)表有很多不同之處，最顯著的地方在于，數(shù)據(jù)庫的數(shù)據(jù)表可以理解為由行列構(gòu)成，而Excel工作表則是由一個(gè)又一個(gè)單元格構(gòu)成，且這些單元格擁有獨(dú)特的地址表述方法，也就是A1或R1C1，它們還可以構(gòu)成數(shù)據(jù)相連的單元格區(qū)域，例如A2:H8。
那么問題來了，如果我們只需要計(jì)算某張Excel工作表的部分區(qū)域的話，SQL該怎么表述呢？
比如，很多人的Excel標(biāo)題行并不是處于表格的第一行，而是第2行……
此時(shí)，我們希望計(jì)算A2:F列的單元格區(qū)域，這樣我們更容易使用字段名處理數(shù)據(jù)，而不是整張Excel工作表……
再比如，一張表里存在兩個(gè)或更多個(gè)“表”……這句話什么意思呢？
見下圖▼
圖中所示的表格中，既存在一份“教師表”，又存在一份“學(xué)生表”；如果我們只希望SQL引用計(jì)算A2:D8的教師表數(shù)據(jù)……
……Excel中的SQL其實(shí)是支持將工作表的單元格區(qū)域作為“表”使用的。
SELECT
姓名,學(xué)科 FROM [數(shù)據(jù)表
$A2
:D8]
而第1種情況，我們知道數(shù)據(jù)開始于A2單元格，但不知道結(jié)束于F列的哪個(gè)單元格，SQL可以寫成：
SELECT
姓名,愛好 FROM [學(xué)生表
$A2
:F]
另外，如果我們需要SQL引用計(jì)算表格D:G整列的數(shù)據(jù)，SQL可以寫成：
SELECT
* FROM [學(xué)生表
$D
:G]
總結(jié)以上幾種Excel工作表區(qū)域的表述方式，也就是，
工作表名稱+美金符號$+相對引用狀態(tài)下的單元格地址，最后使用中括號包起來
。
[學(xué)生表$A2:F]，我們說該語句可以引用從A2至F列最后存在數(shù)據(jù)的單元格區(qū)域，但這是有一個(gè)限制前提條件的，即非自連接狀態(tài)。所謂自連接是指SQL應(yīng)用于鏈接自身的工作簿。自鏈接狀態(tài)下，A2:F的表達(dá)方式最多是A2:F65536行；倘若此時(shí)需要的引用行超過65536行，請使用整表模式。
2.跨工作簿的表
一個(gè)眾所周知的問題是，Excel函數(shù)在處理跨工作薄數(shù)據(jù)時(shí)很是疲態(tài)，除了個(gè)別幾個(gè)查找引用類函數(shù)（例如VLOOKUP等），絕大部分函數(shù)都需要打開相關(guān)工作簿后才可以計(jì)算使用。
是的，VLOOKUP函數(shù)并不需要打開相關(guān)工作簿也可以跨工作薄使用，而且在VLOOKUP公式書寫完成后，即便你把它所引用的工作簿給刪了，也不妨礙它計(jì)算，這是因?yàn)樗呀?jīng)把相關(guān)數(shù)據(jù)緩存到了公式所在的工作簿中，不過VLOOKUP這種模式并不支持函數(shù)復(fù)雜嵌套……打個(gè)響指，關(guān)于這一點(diǎn)，如果你感興趣，我們改天單獨(dú)聊一下。
……我們之前分享的SQL語句都是處理當(dāng)前工作簿的表格，如果我們所需要處理的數(shù)據(jù)位于其它工作簿時(shí)，SQL該怎么表述呢？
例如，獲取位于計(jì)算機(jī)D盤的“EH小學(xué)”文件夾下的“學(xué)生表.xlsx”工作簿中的“成績表”的所有數(shù)據(jù)——一口氣讀完這話的，不得不讓在下心生佩服。
如果是OLE DB法（該方法參考本系列教程第1章），SQL語句如下▼
SELECT
*
FROM
[D:EH小學(xué)學(xué)生表.xlsx]
.
[成績表$]
FROM后指定表字符串有兩個(gè)部分構(gòu)成，
第一個(gè)中括號內(nèi)是指定工作簿的存放路徑+帶后綴的完整工作簿名稱，后一個(gè)中括號內(nèi)是工作表名稱，兩個(gè)中括號之間使用英文點(diǎn)號（.）相連
。
敲書柜前方預(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)建的鏈接是一個(gè)工作簿，需要獲取的數(shù)據(jù)在另一個(gè)或多個(gè)工作簿，例如兩個(gè)工作簿之間的數(shù)據(jù)查詢統(tǒng)計(jì)。此時(shí)通常使用的代碼如下▼
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語句中又指定了另外一個(gè)工作簿的鏈接。SQL語句如下▼
SELECT
*
FROM
[Excel
12.0
;DATABASE=D:EH小學(xué)學(xué)生表.xlsx].[成績表$]
FROM指定表的字符串有兩部分組成。第一個(gè)中括號中，Excel 12.0是目標(biāo)工作簿的版本號，第2章時(shí)我們講過，Excel 12.0適用于除了2003以外的所有Excel版本。DATABASE指定的是數(shù)據(jù)源工作簿的路徑和名稱。第2個(gè)中括號內(nèi)是工作表名。兩個(gè)中括號之間使用英文點(diǎn)號相連。
看起來似乎VBA+ADO方法的SQL語句比OLE DB法更復(fù)雜？
確實(shí)如此，不過前者的功能也更強(qiáng)大。比如，它可以通過VBA對象的屬性、方法，循環(huán)和判斷語句等，有條件的篩選工作簿和工作表……相比之下，OLE DB中的SQL語句就是純手工常量模式了。當(dāng)然，更重要的是，前者不但可以查數(shù)據(jù)，還可以增改刪數(shù)據(jù)，后者卻只限于查。
關(guān)注我，下期再賤。
示例文件下載，百度網(wǎng)盤..▼
https://pan.baidu.com/s/1tVhYyLg2CPDD5EBDoN_vKg
提取碼: et58
需要系統(tǒng)學(xué)習(xí)Excel，卻找不到優(yōu)質(zhì)教程？學(xué)習(xí)Excel的過程中遇到疑難問題，卻找不到人及時(shí)作出解答？加入我的付費(fèi)社群，和微軟最有價(jià)值專家（MVP）全面精進(jìn)Excel，學(xué)習(xí)+答疑都不再是問題……
加入我的Excel會(huì)員，
全面學(xué)習(xí)Excel
透視表 函數(shù) 圖表 VBA PQ想學(xué)啥學(xué)啥
本文由公眾號“Excel星球”首發(fā)。
點(diǎn)擊
閱讀原文
，加入Excel會(huì)員社群！
版權(quán)：如無特殊注明，文章轉(zhuǎn)載自網(wǎng)絡(luò)，侵權(quán)請聯(lián)系cnmhg168#163.com刪除！文件均為網(wǎng)友上傳，僅供研究和學(xué)習(xí)使用，務(wù)必24小時(shí)內(nèi)刪除。
