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
下面的不对啊
不对啊
追答什么问题?
追问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