Question

用T-sql语句写出 查询出平均分大于80分，且至少两门课大于80的学生。 表如下：

create table student
(
id int primary key ,
name varchar(10) not null,
major varchar(10) not null,
score int ,
sex varchar(2) not null
)
go
插入如下数据：
insert into student values(1,'邱君','语文',70,'女')
insert into student values(2,'小狗','语文',76,'男')
insert into student values(3,'混蛋','语文',60,'男')

insert into student values(4,'邱君','数学',81,'女')
insert into student values(5,'混蛋','数学',90,'男')
insert into student values(6,'小狗','数学',77,'男')

insert into student values(7,'邱君','外语',98,'女')
insert into student values(8,'小狗','外语',71,'男')
insert into student values(9,'混蛋','外语',88,'男')
go
输出如下图形：
姓名 数学 外语 语文 平均成绩
邱君 81 98 76 82
下面的不对啊

Answer 1

稍微简化一下：
SELECT NAME,
SUM(CASE WHEN MAJOR = '数学' THEN SCORE ELSE 0 END)数学 ,
SUM(CASE WHEN MAJOR = '外语' THEN SCORE ELSE 0 END)外语 ,
SUM(CASE WHEN MAJOR = '语文' THEN SCORE ELSE 0 END)语文 ,
AVG(SCORE)
FROM STUDENT GROUP BY NAME
having SUM(CASE WHEN SCORE > 80 THEN 1 ELSE 0 END) >= 2
AND AVG(SCORE) >80

Answer 2

SELECT id,
SUM(CASE WHEN major = '数学' THEN score ELSE 0 END) ,
SUM(CASE WHEN major = '外语' THEN score ELSE 0 END) ,
SUM(CASE WHEN major = '语文' THEN score ELSE 0 END) ,
AVG(score)
FROM student
WHERE student.id IN (SELECT id FROM student GROUP BY id HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2)
GROUP BY id HAVING AVG(score) > 80

即可

追问

不对啊

追答

什么问题？

追问

SELECT name,
SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 ,
SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 ,
SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文
FROM student
WHERE name IN (SELECT name FROM student GROUP BY name HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2)
GROUP BY name HAVING AVG(score) > 80

我把你的改了一下就好了 谢谢你的回答！

追答

恩。我也刚发现了。

你的少了一个平均成绩
SELECT name,
SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 ,
SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 ,
SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文,
AVG(score)
FROM student
WHERE name IN (SELECT name FROM student GROUP BY name HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2)
GROUP BY name HAVING AVG(score) > 80

3楼那样要简化点，不过貌似having加多条件过滤后反而慢

SELECT name,
SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 ,
SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 ,
SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文,
AVG(score)
FROM student
GROUP BY name
HAVING AVG(score) > 80 AND SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2

Answer 3

对的吧