第12章 多表連接查詢
在SQL Server數(shù)據(jù)庫(kù)中，各個(gè)數(shù)據(jù)表之間往往有這樣或那樣的聯(lián)系，我們?cè)诓樵償?shù)據(jù)時(shí)也往往需要使用多個(gè)數(shù)據(jù)表，這就是多表連接查詢。
比如，在第7章介紹的“研究生管理”數(shù)據(jù)庫(kù)的5個(gè)數(shù)據(jù)表中，“院系”表和“導(dǎo)師”表之間通過“院系編號(hào)”建立聯(lián)系，“導(dǎo)師”表和“研究生”表之間通過“導(dǎo)師編號(hào)”建立聯(lián)系，“研究生”表、“課程”表和“成績(jī)”表之間通過“學(xué)號(hào)”和“課程代碼”建立聯(lián)系。這樣，我們可以利用這些關(guān)系，查詢需要的數(shù)據(jù)，并使數(shù)據(jù)所含的信息明確化。
12.1 SELECT多表連接查詢的語法結(jié)構(gòu)
在一個(gè)查詢中，當(dāng)需要對(duì)兩個(gè)或多個(gè)表進(jìn)行連接查詢時(shí)，可以指定連接字段，在WHERE子句中給出連接條件，在FROM子句中指定要連接的表，其格式如下：
SELECT 字段名1，字段名2，……
FROM 表1，表2，……
WHERE 連接條件
對(duì)于連接的多個(gè)表，通常存在公共字段，為了區(qū)別該字段是哪個(gè)表的字段，在連接條件中可以通過表名前綴制定連接字段，即“表名.字段名”來區(qū)分哪個(gè)表的字段，例如，“研究生.學(xué)號(hào)”表示表“研究生”的字段“學(xué)號(hào)”，而“成績(jī).學(xué)號(hào)”表示表“成績(jī)”的字段“學(xué)號(hào)”。
在多表連接查詢的SELECT語句中，WHERE連接條件可以是多種多樣的，下面介紹幾種常見的多表連接查詢方法。
12.2 等值或非等值連接查詢
等值連接查詢，就是在WHERE子句中通過等號(hào)連接兩個(gè)表，從而查詢出需要的數(shù)據(jù)。
非等值連接查詢，就是在WHERE子句中通過不等號(hào)（>、<、>=、<=、<>等）連接兩個(gè)表，從而查詢出需要的數(shù)據(jù)。
下面我們通過幾個(gè)例子說明等值或非等值連接查詢的基本方法和程序代碼。
【例12-1】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“研究生”、“課程”和“成績(jī)”中，查詢每個(gè)學(xué)生的姓名、性別和選修的課程名稱以及考試成績(jī)。
在運(yùn)行下面的程序之前，要確保已經(jīng)引用了ADO對(duì)象庫(kù)MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時(shí)，還要根據(jù)電腦實(shí)際情況，將連接字符串中的SQL Server服務(wù)器名稱進(jìn)行變更，并輸入相應(yīng)的用戶名和密碼（如果有的話）。
Public Sub 例12—1()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQLServer服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select 研究生.姓名，研究生.性別，課程.課程名稱，成績(jī).成績(jī) " _
& "from 研究生,課程，成績(jī)" _
& "where 研究生.學(xué)號(hào)=成績(jī).學(xué)號(hào) and 成績(jī).課程代碼=課程.課程代碼 "_
& "order by 研究生.姓名"
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:D1") = Array("姓名","性別", "課程名稱", "成績(jī)")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
MsgBox "數(shù)據(jù)追加成功！", vbInformation
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行上面的程序，就得到如圖12-1所示的結(jié)果。
【例12-2】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“研究生”、“課程”和“成績(jī)”中，查詢研究生“李小菁”的性別和選修的課程名稱以及考試成績(jī)。
此時(shí)，SQL語句如下，程序的其他部分與【例12-1】完全相同。
SQL = “select 研究生.姓名，研究生.性別，課程.課程名稱，成績(jī).成績(jī) ”_
& “ from 研究生，課程，成績(jī)”_
& “where 研究生.學(xué)號(hào)=成績(jī).學(xué)號(hào) and 成績(jī).課程代碼=課程.課程代碼 and 研究生.姓名=‘李小菁’”
運(yùn)行上面的程序，就得到如圖12-2所示的結(jié)果。
【例12-3】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“研究生”、“課程”和“成績(jī)”中，查詢各個(gè)班級(jí)、各個(gè)課程的平均分。
Public Sub 例12—3()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select 課程.課程名稱，研究生.班級(jí)，avg(成績(jī).成績(jī)) as 平均成績(jī) " _
& "from 研究生,課程，成績(jī)" _
& "where 研究生.學(xué)號(hào)=成績(jī).學(xué)號(hào) and 成績(jī).課程代碼=課程.課程代碼 "_
& "group by 研究生.班級(jí)，課程.課程名稱 " _
& "order by 課程.課程名稱"
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:C1") = Array("課程","班級(jí)", "平均成績(jī)")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行程序后的結(jié)果如圖12-3所示。注意，由于在設(shè)計(jì)數(shù)據(jù)表時(shí)，對(duì)成績(jī)分?jǐn)?shù)定義為整數(shù)，因此上述程序滿意計(jì)算的平均分?jǐn)?shù)也為整數(shù)。
圖12-3所示的表格看起來不太方便，我們可以利用條件格式將A列的課程進(jìn)行格式化，格式化后的表格如圖12-4所示。下面是利用條件格式將A列的課程進(jìn)行格式化的程序代碼，這些語句加在語句“Columns.AutoFit”后面：
Range(“A2:A” & Range(“A
Selection.FormatConditions.Delete
Selection.FormatConditions.AddType:=xlExpression,Formula1:=”=SA2=SA
Selection.FormatConditions(1).Font.ColorIndex=2
【例12-4】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“研究生”、“課程”和“成績(jī)”中，查詢選修“微觀材料學(xué)”課程、并且考試分?jǐn)?shù)高于該科平均分的學(xué)生姓名、性別、班級(jí)及考試成績(jī)，并按成績(jī)降序排列。
Public Sub 例12—4()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select 課程.課程名稱，研究生.姓名，研究生.性別，研究生.班級(jí)，成績(jī).成績(jī) " _
& "from 研究生,課程，成績(jī)" _
& "where 研究生.學(xué)號(hào)=成績(jī).學(xué)號(hào) and 成績(jī).課程代碼=課程.課程代碼 "_
& "and 課程.課程名稱=‘微觀材料學(xué)’" _
& "where 成績(jī).課程代碼=課程.課程代碼 and 課程.課程名稱=‘微觀材料學(xué)’)" _
& "order by 成績(jī).成績(jī)desc"
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:E1") = Array("課程","姓名", "性別", "班級(jí)","成績(jī)")
Range("A1:E1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行上面的程序，就得到如圖12-5所示的結(jié)果。
【例12-5】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“課程”和“成績(jī)”中，查詢所有課程的平均成績(jī)，并按降序排列。
Public Sub 例12—5()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select 課程.課程名稱，avg(成績(jī).成績(jī)) as 平均成績(jī) from 課程，成績(jī) " _
& "where 成績(jī).課程代碼=課程.課程代碼 " _
& "group by 課程.課程名稱"_
& "order by 平均成績(jī) desc"
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:B1") = Array("課程名稱","平均成績(jī)")
Range("A1:E1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub.
運(yùn)行上面的程序，就得到如圖12-6所示的結(jié)果。
【例12-6】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“研究生”、“導(dǎo)師”、“課程”和“成績(jī)”中，查詢出選修某課程的學(xué)生人數(shù)多于2人的教師姓名。
Public Sub 例12—6()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select 課程.課程名稱，導(dǎo)師.姓名，COUNT(成績(jī).學(xué)號(hào)) from 導(dǎo)師，課程，成績(jī) " _
& "where 成績(jī).課程代碼=課程.課程代碼 and 導(dǎo)師.導(dǎo)師編號(hào)=課程.授課教師 "_
& "group by 課程.課程名稱，導(dǎo)師.姓名" _
& "having COUNT(成績(jī).學(xué)號(hào))>=3"
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:C1") = Array("課程名稱","教師名稱", "選修人數(shù)")
Range("A1:C1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行上面的程序，就得到如圖12-7所示的結(jié)果。
12.3 自身連接查詢
在數(shù)據(jù)查詢中，有時(shí)候需要將同一個(gè)表進(jìn)行連接，這種連接成為自身連接。進(jìn)行自身連接就如同兩個(gè)分開的表一樣，可以把一個(gè)表的某行與同一表中的另一行連接起來。
【例12-7】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“成績(jī)”中，查詢出選修課程代碼為“200601003”、并且成績(jī)高于學(xué)號(hào)為“A03200602”學(xué)生成績(jī)的所有學(xué)生考試成績(jī)記錄，并按成績(jī)從高到低進(jìn)行排列。
Public Sub 例12—7()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select x.學(xué)號(hào)，x.課程代碼， x.成績(jī)" _
& "from 成績(jī) as x,成績(jī) asy" _
& "where x.課程代碼='200601003' and y.學(xué)號(hào)='A03200602' and x.成績(jī)>y.成績(jī) " _
& "order by x.成績(jī) desc"
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:C1") = Array("學(xué)號(hào)","課程代碼", "成績(jī)")
Range("A1:C1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行上面的程序，就得到如圖12-8所示的結(jié)果。
12.4 內(nèi)連接查詢
內(nèi)連接是常用的連接操作，利用INNER JION……ON連接幾個(gè)表。此時(shí)的SELECT語句結(jié)構(gòu)如下：
SELECT 屬性或字段列表
FROM 表1名 [INNER]JION 表2名
ON 連接條件
[WHERE 限定條件]
這里，INNER可以省略，用ON 指定連接條件，用WHERE指定其他限定條件。
【例12-8】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“成績(jī)”和“研究生”中，查詢出班級(jí)編號(hào)為“A03200601”的全部學(xué)生的信息記錄，包括基本信息和考試信息。
Public Sub 例12—8()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select x.班級(jí)，x.學(xué)號(hào)， x.姓名，x.性別，y.課程代碼，y.成績(jī)" _
& "from 研究生 as x inner join 成績(jī) as y " _
& " on x.學(xué)號(hào) = y.學(xué)號(hào) " _
& "where x.班級(jí)='A03200601' "
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:F1") = Array("班級(jí)","學(xué)號(hào)", "姓名", "性別","課程代碼", "成績(jī)")
Range("A1:F1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行上面的程序，就得到如圖12-9所示的結(jié)果。
12.5 左外連接查詢
左外連接查詢，是指在查詢結(jié)果中包含第一個(gè)表中滿足條件的所有記錄。如果是在連接條件上滿足匹配條件，那么就返回第2個(gè)表相應(yīng)的值，否則就返回空值。此時(shí)的SELECT語句結(jié)構(gòu)如下：
SELECT 屬性或字段列表
FROM 表1名 LEFTOUTER JION 表2名
ON 連接條件
[WHERE 限定條件]
【例12-9】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“導(dǎo)師”和“院系”中，查詢滿足匹配條件的各個(gè)院系具體名稱等信息。
Public Sub 例12—9()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select x.姓名，x.性別， x.職稱，x.院系名" _
& "from 導(dǎo)師 as x left outer join 院系 as y " _
& " on x.院系編號(hào) = y.院系編號(hào) "
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:D1") = Array("姓名","性別", "職稱", "院系")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行程序后的結(jié)果如圖12-10所示。
12.6 右外連接查詢
右外連接查詢，是指在查詢結(jié)果中包含第二個(gè)表中滿足條件的所有記錄。如果是在連接條件上滿足匹配條件，那么就返回第一個(gè)表相應(yīng)的值，否則就返回空值。此時(shí)的SELECT語句結(jié)構(gòu)如下：
SELECT 屬性或字段列表
FROM 表1名 RIGHTOUTER JION 表2名
ON 連接條件
[WHERE 限定條件]
【例12-10】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“導(dǎo)師”和“院系”中，查詢滿足匹配條件的各個(gè)導(dǎo)師的院系具體名稱等信息。
Public Sub 例12—10()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select x.姓名，x.性別， x.職稱，x.院系名" _
& "from 導(dǎo)師 as x right outer join 院系 as y " _
& " on x.院系編號(hào) = y.院系編號(hào) "
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:D1") = Array("姓名","性別", "職稱", "院系")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行程序后的結(jié)果如圖12-11所示。
12.7 全外連接查詢
全外連接查詢，是指在查詢結(jié)果中包含兩個(gè)表中滿足條件的所有記錄。如果是在連接條件上滿足匹配的元組，那么就返回另一個(gè)表相應(yīng)的值，否則另一個(gè)表就返回空值。此時(shí)的SELECT語句結(jié)構(gòu)如下：
SELECT 屬性或字段列表
FROM 表1名 FULLOUTER JION 表2名
ON 連接條件
[WHERE 限定條件]
【例12-11】下面的例子是從數(shù)據(jù)庫(kù)“研究生管理”的數(shù)據(jù)表“導(dǎo)師”和“院系”中，查詢滿足匹配條件的各個(gè)導(dǎo)師的院系具體名稱等信息。
Public Sub 例12—11()
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
'建立與SQL Server服務(wù)器的連接
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "password=11111;" _
& "Data Source=THTFCOMPUTER;" _
& "Initial Catalog=研究生管理"
cnn.Open
'查詢數(shù)據(jù)
SQL = " select x.姓名，x.性別， x.職稱，x.院系名" _
& "from 導(dǎo)師 as x full outer join 院系 as y " _
& " on x.院系編號(hào) = y.院系編號(hào) "
Set rs = cnn.Execute(SQL)
'復(fù)制查詢出的數(shù)據(jù)
Cells.Clear
Range("A1:D1") = Array("姓名","性別", "職稱", "院系")
Range("A1:D1").Font.Bold = True
Range("A2").ColumnDifferences rs
Columns.AutoFit
'關(guān)機(jī)記錄集以及與數(shù)據(jù)庫(kù)的連接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
運(yùn)行程序后的結(jié)果如圖12-11所示。
聯(lián)系客服
