国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

第10章 匯總和分組查詢

要想將一組數(shù)據(jù)轉(zhuǎn)換為有用的信息，就需要對數(shù)據(jù)進行分類匯總，即按照一定的方法對數(shù)據(jù)進行分組并進行匯總計算。在SQL語句中，我們可以使用聚合函數(shù)進行匯總計算，并使用GROUP BY子句和HAVING子句對結(jié)果進行分組。

10.1 聚合函數(shù)

SQL提供了一組聚合函數(shù)，它們能夠?qū)φ麄€數(shù)據(jù)集合進行計算，并返回一行包含原始數(shù)據(jù)集體匯總結(jié)果的記錄，常用的聚合函數(shù)如表10-1所示。

10.2 使用聚合函數(shù)進行匯總查詢

使用聚合函數(shù)進行匯總查詢的SQL語句結(jié)構(gòu)如下。

SQL = “select 聚合函數(shù)（字段名） AS 別名 from 數(shù)據(jù)表名 where 條件表達式”

【例10-1】下面的例子是從數(shù)據(jù)庫“研究生管理”中的數(shù)據(jù)表“研究生”中，查詢班級“A03200601”的總?cè)藬?shù)，并將人數(shù)數(shù)據(jù)輸出到工作表中。

在運行下面的程序之前，要確保已經(jīng)引用了ADO對象庫MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時，還要根據(jù)電腦實際情況，將連接字符串中的SQL Server服務器名稱進行變更，并輸入相應的用戶名和密碼（如果有的話）。

Public Sub 例10—1()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim SQL As String, mydata As String, mytable As String

  mydata = "研究生管理"      '指定數(shù)據(jù)庫

  mytable = "研究生"     '指定數(shù)據(jù)表

   '清除工作表的所有數(shù)據(jù)

  Cells.Clear

   '建立與SQL Server服務器的連接

  cnn.ConnectionString = "Provider=SQLOLEDB;" _

       & "User ID=sa;" _

       & "password=11111;" _

       & "Data Source=THTFCOMPUTER;" _

       & "Initial Catalog=" & mydata

   cnn.Open

    '查詢指定班級的研究生人數(shù)

   SQL = " select count(*) as 'A03200601' from " & mytable& " owhere 班級='A03200601'"

   Set rs = cnn.Execute(SQL)

    '在工作表中輸出查詢結(jié)果

   Range("A1") = "A03200601班級人數(shù)為："& rs!A03200601班級人數(shù) & "人"

    '關(guān)機記錄集以及與數(shù)據(jù)庫的連接

   rs.Close

   cnn.Close

   Set rs = Nothing

   Set cnn = Nothing

End Sub

【例10-2】下面的例子是從數(shù)據(jù)庫“研究生管理”中的數(shù)據(jù)表“成績”中，查詢最高分的學生學號和課程代碼，并將結(jié)果輸出到工作表中。

在運行下面的程序之前，要確保已經(jīng)引用了ADO對象庫MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時，還要根據(jù)電腦實際情況，將連接字符串中的SQL Server服務器名稱進行變更，并輸入相應的用戶名和密碼（如果有的話）。

Public Sub 例10—2()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim SQL As String, mydata As String, mytable As String

  Dim maxScore As Integer

  mydata = "研究生管理"      '指定數(shù)據(jù)庫

  mytable = "成績"     '指定數(shù)據(jù)表

   '建立與SQL Server服務器的連接

  cnn.ConnectionString = "Provider=SQLOLEDB;" _

       & "User ID=sa;" _

       & "password=11111;" _

       & "Data Source=THTFCOMPUTER;" _

       & "Initial Catalog=" & mydata

   cnn.Open

    '查詢最高分，并賦給變量maxScore

   SQL = " select max(成績) as from " & mytable

    Setrs = cnn.Execute(SQL)

   maxScore = rs.Fields("最高分")

    '在工作表中輸出查詢結(jié)果

   Cells.Clear

   Range("A1:C1") = Array("學號","課程代碼", "最高分")

   SQL = "select 學號,課程代碼,成績 from " & mytable & "where 成績=" & maxScore

   Set rs = New ADODB.Recordset

   Set rs = cnn.Execute(SQL)

   Range("A2").CopyFromRecordset rs

    '關(guān)機記錄集以及與數(shù)據(jù)庫的連接

   cnn.Close

   Set rs = Nothing

   Set rsx = Nothing

   Set cnn = Nothing

End Sub

由于“成績”數(shù)據(jù)表中保存了所有課程的考試成績，可能會存在幾個相同的最高分，因此在程序中首先將最高分查詢出來，然后再查詢出最高分對應的學號和課程代碼。運行上面的程序，就得到如圖10-1所示的結(jié)果。

【例10-3】下面的例子是從數(shù)據(jù)庫“研究生管理”中的數(shù)據(jù)表“成績”中，查詢課程代碼為 “200601003”的平均分，并將結(jié)果輸出到工作表中。

在運行下面的程序之前，要確保已經(jīng)引用了ADO對象庫MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時，還要根據(jù)電腦實際情況，將連接字符串中的SQL Server服務器名稱進行變更，并輸入相應的用戶名和密碼（如果有的話）。

Public Sub 例10—3()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim SQL As String, mydata As String, mytable As String

  Dim maxScore As Integer

  mydata = "研究生管理"      '指定數(shù)據(jù)庫

  mytable = "成績"     '指定數(shù)據(jù)表

   '建立與SQL Server服務器的連接

  cnn.ConnectionString = "Provider=SQLOLEDB;" _

       & "User ID=sa;" _

       & "password=11111;" _

       & "Data Source=THTFCOMPUTER;" _

       & "Initial Catalog=" & mydata

   cnn.Open

    '查詢指定班級的研究生人數(shù)

   SQL = " select avg(成績) as 平均分 from" & mytable & "where 課程代碼='200601003'"

   Set rs = cnn.Execute(SQL)

   maxScore = rs.Fields("最高分")

    '在工作表中輸出查詢結(jié)果

   Cells.Clear

   Range("A1") = "課程代碼200601003的平均分為： " & rs!平均分

    '關(guān)機記錄集以及與數(shù)據(jù)庫的連接

   cnn.Close

   Set rs = Nothing

   Set cnn = Nothing

End Sub

10.3 使用GROUP BY子句進行分組查詢

在使用聚合函數(shù)時，我們還可以加上GROUP BY子句對查詢結(jié)果進行分組。比如，按部門進行銷售額求和、按性別計算平均工資、計算每個班級人數(shù)等。

帶有GROUP BY子句的SELECT語句結(jié)構(gòu)如下：

Select 字段列表

  FROM 數(shù)據(jù)表名

    WHERE 條件表達式

       GROUP BY 字段名或表達式

【例10-4】下面的例子是從數(shù)據(jù)庫“研究生管理”中的數(shù)據(jù)表“研究生”中，查詢各個班級的人數(shù)，并將結(jié)果輸出到工作表中。

在運行下面的程序之前，要確保已經(jīng)引用了ADO對象庫MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時，還要根據(jù)電腦實際情況，將連接字符串中的SQL Server服務器名稱進行變更，并輸入相應的用戶名和密碼（如果有的話）。

Public Sub 例10—4()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim SQL As String, mydata As String, mytable As String

  mydata = "研究生管理"      '指定數(shù)據(jù)庫

  mytable = "研究生"     '指定數(shù)據(jù)表

   '建立與SQL Server服務器的連接

  cnn.ConnectionString = "Provider=SQLOLEDB;" _

       & "User ID=sa;" _

       & "password=11111;" _

       & "Data Source=THTFCOMPUTER;" _

       & "Initial Catalog=" & mydata

   cnn.Open

    '查詢指定班級的研究生人數(shù)

   SQL = " select 班級,count(*) as 人數(shù) from" & mytable & "group by 班級"

   Set rs = cnn.Execute(SQL)

    '在工作表中輸出查詢結(jié)果

   Range("A1:B1") = Array("班級","人數(shù)")

   Range("A2").CopyFromRecordset rs

    '關(guān)機記錄集以及與數(shù)據(jù)庫的連接

   rs.Close

   cnn.Close

   Set rs = Nothing

   Set cnn = Nothing

End Sub

運行上面的程序，就得到如圖10-2所示的結(jié)果。

10.4 使用HAVING子句進行分組查詢

在使用聚合函數(shù)時，我們還可以聯(lián)合使用GROUP BY子句和HAVING子句對藍魔結(jié)果進行嚴格的分組。比如，查詢匯總銷售總額大于平均值的各個部門名稱及其銷售額總額、查詢平均分大于80分的班級等。

帶有GROUP BY子句和HVING子句的SELECT語句結(jié)構(gòu)如下：

SELECT 字段列表

  FROM 數(shù)據(jù)表名

    WHERE 條件表達式

      GROUP BY 字段名或表達式

        HAVING 篩選表達式

【例10-5】下面的例子是從數(shù)據(jù)庫“研究生管理”中的數(shù)據(jù)表“成績”中，查詢平均分大于85分的各個課程代碼及其平均分，按平均分降序排序，并將結(jié)果輸出到工作表中。

在運行下面的程序之前，要確保已經(jīng)引用了ADO對象庫MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時，還要根據(jù)電腦實際情況，將連接字符串中的SQL Server服務器名稱進行變更，并輸入相應的用戶名和密碼（如果有的話）。

Public Sub 例10—5()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim SQL As String, mydata As String, mytable As String

  mydata = "研究生管理"      '指定數(shù)據(jù)庫

  mytable = "成績"     '指定數(shù)據(jù)表

   '建立與SQL Server服務器的連接

  cnn.ConnectionString = "Provider=SQLOLEDB;" _

       & "User ID=sa;" _

       & "password=11111;" _

       & "Data Source=THTFCOMPUTER;" _

       & "Initial Catalog=" & mydata

   cnn.Open

    '查詢指定班級的研究生人數(shù)

   SQL = " select 課程代碼,avg(成績) as 平均分 from " & mytable _

       & "group by 課程代碼 having avg(成績)>85order by desc"

   Set rs = cnn.Execute(SQL)

    '在工作表中輸出查詢結(jié)果

   Cells.Clear

   Range("A1:B1") = Array("課程代碼","平均分")

   Range("A2").CopyFromRecordset rs

    '關(guān)機記錄集以及與數(shù)據(jù)庫的連接

   rs.Close

    cnn.Close

   Set rs = Nothing

   Set cnn = Nothing

End Sub

運行上面的程序，就得到如圖10-3所示的結(jié)果。

【例10-6】下面的例子是從數(shù)據(jù)庫“研究生管理”中的數(shù)據(jù)表“成績”中，查詢至少有3名學生選修、并且以“1003”結(jié)尾的課程代碼，并將結(jié)果輸出到工作表中。

在運行下面的程序之前，要確保已經(jīng)引用了ADO對象庫MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時，還要根據(jù)電腦實際情況，將連接字符串中的SQL Server服務器名稱進行變更，并輸入相應的用戶名和密碼（如果有的話）。

Public Sub 例10—6()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim SQL As String, mydata As String, mytable As String

  mydata = "研究生管理"      '指定數(shù)據(jù)庫

  mytable = "成績"     '指定數(shù)據(jù)表

   '建立與SQL Server服務器的連接

  cnn.ConnectionString = "Provider=SQLOLEDB;" _

       & "User ID=sa;" _

       & "password=11111;" _

       & "Data Source=THTFCOMPUTER;" _

       & "InitialCatalog=" & mydata

   cnn.Open

    '查詢指定班級的研究生人數(shù)

   SQL = " select 課程代碼,COUNT(*) as 選修人數(shù) from" & mytable _

       & "where 課程代碼 like '%1003' group by 課程代碼 having COUNT(*)>3"

   Set rs = cnn.Execute(SQL)

    '在工作表中輸出查詢結(jié)果

   Cells.Clear

    Range("A1:B1") = Array("課程代碼", "選修人數(shù)")

   Range("A2").CopyFromRecordset rs

    '關(guān)機記錄集以及與數(shù)據(jù)庫的連接

   rs.Close

   cnn.Close

   Set rs = Nothing

   Set cnn = Nothing

End Sub

運行上面的程序，就得到如圖10-4所示的結(jié)果。

【10-7】下面的例子是從數(shù)據(jù)庫“研究生管理”中的數(shù)據(jù)表“成績”中，查詢最低分大于80分、最高分小于95分的課程代碼，并將結(jié)果輸出到工作表中。

在運行下面的程序之前，要確保已經(jīng)引用了ADO對象庫MicrosoftActive Data Objects 2.5 Library或其他版本（如Microsoft Active Data Objects 2.1 Library、Microsoft Active Data Objects 2.8 Library等）。同時，還要根據(jù)電腦實際情況，將連接字符串中的SQL Server服務器名稱進行變更，并輸入相應的用戶名和密碼（如果有的話）。

Public Sub 例10—7()

  Dim cnn As New ADODB.Connection

  Dim rs As ADODB.Recordset

  Dim SQL As String, mydata As String, mytable As String

  mydata = "研究生管理"      '指定數(shù)據(jù)庫

  mytable = "成績"     '指定數(shù)據(jù)表

   '建立與SQL Server服務器的連接

  cnn.ConnectionString = "Provider=SQLOLEDB;" _

       & "User ID=sa;" _

       & "password=11111;" _

       & "Data Source=THTFCOMPUTER;" _

       & "Initial Catalog=" & mydata

   cnn.Open

    '查詢指定班級的研究生人數(shù)

   SQL = " select 課程代碼 from " & mytable _

       & " group by 課程代碼 having min(成績)>80and max(成績)<95"

   Set rs = cnn.Execute(SQL)

    '在工作表中輸出查詢結(jié)果

   Cells.Clear

   Range("A1:B1") = Array("課程代碼")

   Range("A2").CopyFromRecordset rs

    '關(guān)機記錄集以及與數(shù)據(jù)庫的連接

   rs.Close

   cnn.Close

   Set rs = Nothing

   Set cnn = Nothing

End Sub

運行上面的程序，就得到如圖10-5所示的結(jié)果。