1、正版可修改PPT課件（中職）計算機組裝與維護A0506教學課件SQL Server數據庫項目教程教學課件項目5：操作數據我們畢業啦其實是答辯的標題地方任務6 簡單查詢之Group by子句知識準備聚合函數應用Group-By分組WHERE和HAVING條件CONTANTS思維導圖1.知識準備1.1聚合函數聚合函數對一組值執行計算，并返回單個值。 除了COUNT外，聚合函數都會忽略 Null 值。 聚合函數經常與 SELECT 語句的GROUP BY子句一起使用。Sql Server 2019 的聚合函數在AVG、COUNT、MAX、MIN、SUM、COUNT_BIG、GROUPING、BINA

2、RY_CHECKSUM、CHECKSUM_AGG、CHECKSUM、STDEV、STDEVP、VAR、VARP等。其中AVG、COUNT、MAX、MIN和SUM是常用的聚合函數1.1聚合函數名稱描述AVG返回指定組中的平均值，空值被忽略。 例：select pID,avg(qPrice) from shops group by pIDCOUNT返回指定組中項目的數量。 例：select count(pID) from shopsMAX返回指定數據的最大值。 例：select pID,max(qPrice) from shops group by pIDMIN返回指定數據的最小值。 例：sele

3、ct pID,min(qPrice) from shops group by pIDSUM 返回指定數據的和，只能用于數字列，空值被忽略。 例：select pID,sum(qPrice) from shops group by pID1.2 GROUP BY 分組將查詢結果劃分為多個行組的 SELECT 語句子句，通常用于在每個組上執行一個或多個聚合。 SELECT 語句每組返回一行。對于group by后面的列表有嚴格的要求：列必須出現在 SELECT 語句的 FROM 子句中，但不要求出現在 SELECT 列表中。 但是 列表中任何非聚合表達式中的每個表列或視圖列都必須包括在 GROUP

4、 BY 列表中。SELECT 列表中定義的列不能使用別名。1.3 HAVING條件和WHERE條件where 子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾數據，條件中不能包含聚合函數，使用where條件顯示特定的行。having 子句的作用是篩選滿足條件的組，即在分組之后過濾數據，條件中經常包含聚合函數，使用having 條件顯示特定的組，也可以使用多個分組標準進行分組。WHERE語句在GROUP BY語句之前；SQL會在分組之前計算WHERE語句。HAVING語句在GROUP BY語句之后；SQL會在分組之后計算HAVING語句。如果有where條件、

5、group by分組和having等參數，請注意它們的順序：先是where,group by ,having，最后才是order by。2.聚合函數應用在數據庫dbStudentScore中，完成下面的查詢操作。（1）在班級表tbClass中計算新校區共有多少個班級。（2）在班級表tbClass中計算新校區各班級的班級總人數。（3）在成績表tbScore中計算課程代碼為“kc90018”課程的總評成績進行計算，計算最高分，最低分，平均分。本案例配備微課視頻2.1 任務描述綜合應用聚合函數可以完成子任務1-3。聚合函數對一組值執行計算，并返回單個值。 除了COUNT外，聚合函數都會忽略 Null

6、 值。 聚合函數經常與 SELECT 語句的GROUP BY子句一起使用。其中AVG、COUNT、MAX、MIN和SUM是常用的聚合函數2.2 任務分析-1.1在班級表tbClass中計算新校區共有多少個班級。select count(*) as total from tbClass where Campus=新校區go-通常使用*或者主鍵列進行統計select count(ClassID) as total from tbClass where Campus=新校區go-1.2在班級表tbClass中計算新校區各班級的班級總人數。select sum(StudentTotals) as to

7、tal from tbClass where Campus=新校區Go-1.3在成績表tbScore中計算課程代碼為“kc90018”課程的總評成績進行計算，計算最高分，最低分，平均分。 select max(ScoreAll ) as maxScore,min(ScoreAll ) as minScore, avg(ScoreAll ) as avgScore from tbScore where CourseID=kc90018 go參考答案3. Group-By分組在數據庫dbStudentScore中，完成下面的查詢操作。（4）在學生表tbStudent中，按性別統計男生和女生的學生總

8、人數。（5）在班級表tbClass中，按校區統計各班級的班級總人數。（6）在成績表tbScore中，按課程代碼統計各個課程的總評成績大于或者等于70分以上的學生人數。本案例配備微課視頻3.1 任務描述應用聚合函數和分組可以完成子任務4-6。將查詢結果劃分為多個行組的 SELECT 語句子句，通常用于在每個組上執行一個或多個聚合。 SELECT 語句每組返回一行。對于GROUP BY后面的列表有嚴格的要求：列必須出現在 SELECT 語句的 FROM 子句中，但不要求出現在 SELECT 列表中。 但是 列表中任何非聚合表達式中的每個表列或視圖列都必須包括在 GROUP BY 列表中。SELEC

9、T 列表中定義的列不能使用別名。3.2任務分析-2.1在學生表tbStudent中，按性別統計男生和女生的學生總人數。select sex, count(StudentID) as total from tbStudent group by sexgo -2.2在班級表tbClass中，按校區統計各班級的班級總人數。select Campus,sum(StudentTotals) as total from tbClass group by Campusgo-2.3在成績表tbScore中，按課程代碼統計各個課程的總評成績大于或者等于70分以上的學生人數。select CourseID,cou

10、nt(*) as total from tbScore where ScoreAll=70 group by CourseIDgo參考答案4. WHERE和HAVING條件在數據庫dbStudentScore中，完成下面的查詢操作。（7）在成績表tbScore中對每門課程的總評成績計算最高分，最低分，平均分，并把平均分大于等于65 分的數據顯示出來。（8）在學生表tbStudent中，計算各個班級團員人數，并把班級團員人數大于等于20人的班級顯示出來。（9）按學校的規定，超過50個學生的課程為大班教學課程，學校會對老師的課酬進行加權計算。在課程教學任務表tbTeachingTasks中，統計第

11、1學期大班教學課程情況，顯示課程代碼，教師工號，選修學生人數。（10）身份證號碼規定前1、2位數字表示所在省份的代碼。在學生表tbStudent中統計各省份的男生超過10人的人數情況，按2位省份代碼升序排序，顯示2位省份代碼和學生人數。4.1 任務描述應用WHERE條件和HAVING條件可以完成子任務7-11。WHERE 子句的作用是在對查詢結果進行分組前，將不符合WHERE條件的行去掉，即在分組之前過濾數據，條件中不能包含聚組函數，使用WHERE條件顯示特定的行。HAVING 子句的作用是篩選滿足條件的組，即在分組之后過濾數據，條件中經常包含聚組函數，使用HAVING 條件顯示特定的組，也可

12、以使用多個分組標準進行分組。本案例配備微課視頻4.2 任務分析WHERE語句在GROUP BY語句之前；SQL會在分組之前計算WHERE語句。HAVING語句在GROUP BY語句之后；SQL會在分組之后計算HAVING語句。HAVING 通常與 GROUP BY 或者聚合函數一起使用，WHERE通常不與聚合函數一起使用。如果有WHERE條件、GROUP BY分組和HAVING等參數，請注意它們的順序：先是WHERE,GROUP BY ,HAVING，最后才是ORDER BY。4.2 任務分析-3.1在成績表tbScore中對每門課程的總評成績計算最高分，最低分，平均分，并把平均分大于等于65

13、 分的數據顯示出來。select CourseID,max(ScoreAll) as maxScore,min(ScoreAll) as minScore,avg(ScoreAll) as avgScorefrom tbScoregroup by CourseID having avg(ScoreAll)65go-3.2在學生表tbStudent中，計算各個班級團員人數，并把班級團員人數大于等于20人的班級顯示出來。select ClassID ,count(StudentID) as total from tbStudent where LeagueMember=是 group by Cla

14、ssID having count(StudentID)=20go參考答案- 3.3按學校的規定，超過50個學生的課程為大班教學課程，學校會對老師的課酬進行加權計算。在課程教學任務表tbTeachingTasks中，統計第1學期大班教學課程情況，顯示課程代碼，教師工號，選修學生人數。select CourseID,TeacherID,count(TaskID) as total from tbTeachingTaskswhere Semester=1group by CourseID,TeacherIDhaving count(TaskID)50Go-3.4身份證號碼規定前1、2位數字表示所在省份的代碼。在學生表tbStudent中統計各省份的男生超過10人的人數情況