SQL Server应用教程第4章 数据库的查询和视图.ppt

4.1.5 数据汇总 执行结果如图4.21所示。 图4.21 查询结果 【例4.41】在XSCJ数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数及男生总数、女生总数、学生总人数。 SELECT 专业名, 性别 , COUNT(*) AS 人数 FROM XS GROUP BY 专业名,性别 WITH CUBE 4.1.5 数据汇总 执行结果如图4.22所示。 汇总行，计算机专业总人数 汇总行，通信工程专业总人数 汇总行，学生总人数 汇总行，女生总人数 汇总行，男生总人数 图4.22 查询结果 分析：本例中用于分组的列（即GROUP BY子句中的列）为专业名和性别，在XS表中，专业名有两个不同的值（计算机、通信工程），性别也有两个不同的值（0、1），再加上NULL值，因此它们可能的组合有5种，因此生成5个汇总行。 4.1.5 数据汇总 【例4.42】在XSCJ数据库上产生一个结果集，包括各专业每门课程的平均成绩、每门课程的总平均成绩、每个专业的总平均成绩和所有课程的总平均成绩。 SELECT 课程名,专业名, AVG(成绩) AS 平均成绩 FROM XS_KC, KC, XS WHERE XS_KC.课程号 = KC.课程号 AND XS_KC.学号 = XS.学号 GROUP BY 课程名,专业名 WITH CUBE 执行结果如图4.23所示。 图4.23 查询结果 4.1.5 数据汇总 【例4.43】统计各专业男生、女生人数及学生总人数，标志汇总行。 SELECT 专业名, 性别 , COUNT(*) AS 人数, GROUPING(专业名) AS spec, GROUPING(性别) AS sx FROM XS GROUP BY 专业名,性别 WITH CUBE 执行结果如图4.24所示。 图4.24 统计各专业男生、女生人数及学生总人数，标志汇总行 4.1.5 数据汇总 3. HAVING子句 使用GROUP BY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据进行进一步的筛选。HAVING子句有一个与WHERE子句类似的形式。 例如查找XSCJ数据库中平均成绩在85分以上的学生，就是在XS_KC数据库上按学号分组后筛选出符合平均成绩大于等于85的学生。HAVING子句的格式为： [ HAVING search_condition ] 其中search_condition为查询条件，与WHERE子句的查询条件类似，并且可以使用聚合函数。 【例4.44】查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。 SELECT 学号 , AVG(成绩) AS 平均成绩 FROM XS_KC GROUP BY 学号 HAVING AVG(成绩) =85 执行结果如图4.25所示。 图4.25平均成绩在85分以上的学生的学号和平均成绩 4.1.5 数据汇总 【例4.45】查找选修课程超过2门且成绩都在80分以上的学生的学号。 SELECT 学号 FROM XS_KC WHERE 成绩 = 80 GROUP BY 学号 HAVING COUNT(*) 2 分析：本查询将XS_KC表中成绩大于80的记录按学号分组，对每组记录计数，选出记录数大于2的各组的学号值形成结果表。 【例4.46】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。 SELECT 学号,AVG(成绩) AS 平均成绩 FROM XS_KC WHERE 学号 IN ( SELECT 学号 FROM XS WHERE 专业名 = 通信工程 ) GROUP BY 学号 HAVING AVG(成绩) =85 4.1.5 数据汇总 分析：先执行WHERE查询条件中的子查询，得到通信工程专业所有学生的学号集；然后对XS_KC中的每条记录，判断其学号字段值是否在前面所求得的学号集中。若否，则跳过该记录，继续处理下一条记录，