求解： sql 数据库 检索各科成绩均大于等于该科平均成绩的学生的学号和姓名

Question

有三个表：学生表（学生号，姓名，性别，年龄）
课程表（课程号，课程名，）
成绩表（学生号，课程号，成绩）
要检索各科成绩均大于等于该科平均成绩的学生的学号和姓名

感觉比较难，希望您可以帮到我！分不多，别见外哦，呵呵呵

Answer 1

CREATE TABLE #学生表(
学生号 INT,
姓名 VARCHAR(10),
性别 VARCHAR(2),
年龄 INT
);

CREATE TABLE #课程表 (
课程号 INT,
课程名 VARCHAR(10)
);

CREATE TABLE #成绩表(
学生号 INT,
课程号 INT,
成绩 INT
);

INSERT INTO #学生表
SELECT 1, '张三', '男', 16 UNION ALL
SELECT 2, '李四', '男', 17 UNION ALL
SELECT 3, '王五', '女', 16 UNION ALL
SELECT 4, '赵六', '女', 17 UNION ALL
SELECT 5, '田七', '女', 18
;

INSERT INTO #课程表
SELECT 1, '语文' UNION ALL
SELECT 2, '数学' UNION ALL
SELECT 3, '外语'
;

-- 张三全部 超过平均。
-- 李四全部 低于平均。
-- 王五 赵六 部分高于，部分低于
-- 田七 少考一门，其它超过平均
INSERT INTO #成绩表
SELECT 1, 1, 85 UNION ALL
SELECT 1, 2, 85 UNION ALL
SELECT 1, 3, 85 UNION ALL
SELECT 2, 1, 75 UNION ALL
SELECT 2, 2, 75 UNION ALL
SELECT 2, 3, 75 UNION ALL
SELECT 3, 1, 85 UNION ALL
SELECT 3, 2, 75 UNION ALL
SELECT 3, 3, 85 UNION ALL
SELECT 4, 1, 75 UNION ALL
SELECT 4, 2, 85 UNION ALL
SELECT 4, 3, 75 UNION ALL
SELECT 5, 1, 83 UNION ALL
SELECT 5, 2, 83
;

SELECT
#学生表.学生号,
#学生表.姓名
FROM
#学生表
WHERE
NOT EXISTS(
SELECT
1
FROM
(
SELECT
课程号,
AVG(成绩) AS 平均成绩
FROM
#成绩表 a
GROUP BY
课程号
) AS 平均成绩表
LEFT JOIN #成绩表
ON (平均成绩表.课程号 = #成绩表.课程号
AND 平均成绩表.平均成绩 < #成绩表.成绩
AND #成绩表.学生号 = #学生表.学生号)
WHERE
学生号 IS NULL
);

学生号 姓名
----------- ----------
1 张三

(1 行受影响)追问

求甚解，我只能“谨慎”的说您这个在我的表中是正确的，因为我看不怎么懂这个：
WHERE 学生号 IS NULL

难道再没有稍微简单一点的做法吗？

追答

-- 这一部分,是查询每一个课程的平均成绩
(SELECT
课程号,
AVG(成绩) AS 平均成绩
FROM
#成绩表 a
GROUP BY
课程号
) AS 平均成绩表

-- 这个是将 平均成绩 与 成绩表 关联.
LEFT JOIN #成绩表

-- 这里是关联条件
-- 课程号匹配. 成绩要大于平均成绩
-- 学生号的条件,是和外面的学生号关联.
ON (平均成绩表.课程号 = #成绩表.课程号
AND 平均成绩表.平均成绩 < #成绩表.成绩
AND #成绩表.学生号 = #学生表.学生号)

这个子查询的结果,就是将每一个学生的成绩,都与平均成绩作比较.
如果成绩 大于 平均成绩,那么 关联结果中,包含 学生号
如果成绩 小于等于平均成绩,那么 关联结果中, 不包含 学生号,也就是那个 学生号的地方,是 NULL

你可以单独执行中间的那段子查询。

SELECT
平均成绩表.课程号,
平均成绩表.平均成绩,
#成绩表.成绩,
#成绩表.学生号
FROM
(SELECT
课程号,
AVG(成绩) AS 平均成绩
FROM
#成绩表 a
GROUP BY
课程号
) AS 平均成绩表
LEFT JOIN #成绩表
ON (平均成绩表.课程号 = #成绩表.课程号
AND 平均成绩表.平均成绩 < #成绩表.成绩
AND #成绩表.学生号 = 1)

当学生号是1，也就是全部都超过平均成绩的，所有数据都显示。
当学生号是其他的数字的时候，不满足条件的，成绩表的部分是 NULL.

那么最后的那个

WHERE
学生号 IS NULL

是和 前面的
NOT EXISTS(

结合起来使用的。
意思就是

不存在有
学生号是 NULL 的数据。

那么就是所有的成绩都超过 平均成绩了。

假如有一门成绩没超过，那么就会存在有 学生号是 NULL 的，那么就不会显示在最后的结果当中。

至于 "难道再没有稍微简单一点的做法吗？"
我想应该是有的，只不过我的水平不高，也就只能写出这个程度的SQL了。

Answer 2

select 学生号,姓名 from (
select a.学生号,a.课程号,b.姓名,a.成绩 from 成绩表 a,学生表 b,课程表 c
where a.学生号=b.学生号 and a.课程号=c.课程号
and EXISTS
(select * from 成绩表
where 学生号=a.学生号
and 课程号=a.课程号
and 成绩>(select avg(成绩) from 成绩表 where 课程号=a.课程号
)
)
) t
group by 学生号,姓名
having count(*)=(select count(*) from 成绩表 where 学生号=t.学生号)

Answer 3

select 学生号，姓名 from 学生表 as a，课程表 as b，成绩表 as c
where a.学生号= c.学生号 and b.课程号=c.课程号 and c。成绩>= (select avg(成绩) from 成绩表)

不知道看的懂不？ 没试，不过应该可以。