1.根据下面3张表格,按要求使用SQL语句实现结果查询
| class_no | st_no | st_name |
|---|---|---|
| c1 | st1 | Sam |
| c1 | st2 | lucy |
| c2 | st3 | jean |
| c2 | st4 | lock |
| c2 | st5 | lily |
| st_no | subject_no | score |
|---|---|---|
| st1 | 1 | 72 |
| st1 | 2 | 89 |
| st1 | 3 | 78 |
| st3 | 1 | 87 |
| st3 | 3 | 89 |
| st4 | 1 | 98 |
| st4 | 2 | 94 |
| subject_no | subject_name |
|---|---|
| 1 | Chinese |
| 2 | mathmatics |
| 3 | English |
(1) 查询每个班级各科成绩总和
(2) 查询每个班级语文成绩大于85分人数
(3) 查询语文成绩大于数学成绩的姓名和归属班级
解:
(1)
SELECT 班级信息表.[class_no] , SUM(成绩表.score) AS 成绩总和
FROM 成绩表 LEFT JOIN 班级信息表 ON 班级信息表.[st_no] = 成绩表.[st_no]
GROUP BY 班级信息表.[class_no];
(2)
SELECT 班级信息表.[class_no] , COUNT(成绩表.[st_no]) AS 人数 FROM (成绩表 LEFT JOIN 班级信息表 ON 成绩表.[st_no] = 班级信息表.[st_no]) LEFT JOIN 课程表 ON 成绩表.[subject_no] = 课程表.[subject_no] WHERE 课程表.[subject_name] = "Chinese" AND 成绩表.score > 85 GROUP BY 班级信息表.[class_no] ;
(3)
SELECT * FROM (SELECT 班级信息表.[class_no] , 班级信息表.[st_name] , SUM(IIf(课程表.[subject_name] = "Chinese",成绩表.score,NULL)) AS 语文成绩 , SUM(IIf(课程表.[subject_name] = "mathmatics",成绩表.score,NULL)) AS 数学成绩 FROM (班级信息表 LEFT JOIN 成绩表 ON 成绩表.[st_no] = 班级信息表.[st_no]) LEFT JOIN 课程表 ON 成绩表.[subject_no] = 课程表.[subject_no] GROUP BY 班级信息表.[st_name] , 班级信息表.[class_no]) WHERE 语文成绩 > 数学成绩;
原文：https://www.cnblogs.com/eternal-immortal/p/9482705.html