27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT [name] AS 姓名,
MAX( CASE subject WHEN '语文' THEN score END) AS 语文,
MAX( CASE subject WHEN '数学' THEN score END) AS 数学,
MAX( CASE subject WHEN '英语' THEN score END) AS 英语
FROM student
GROUP BY [name]
SELECT [name] AS 姓名,
MAX( CASE subject WHEN '语文' THEN
(CASE WHEN score>=80 THEN '优' WHEN score<80 AND score >=60 THEN '良' WHEN score<60 THEN '差' END )
END) AS 语文,
MAX( CASE subject WHEN '数学' THEN
(CASE WHEN score>=80 THEN '优' WHEN score<80 AND score >=60 THEN '良' WHEN score<60 THEN '差' END )
END) AS 数学,
MAX( CASE subject WHEN '英语' THEN
(CASE WHEN score>=80 THEN '优' WHEN score<80 AND score >=60 THEN '良' WHEN score<60 THEN '差' END )
END) AS 英语
FROM student
GROUP BY [name]
--2000觉得长,只有把优良差先处理了
SELECT [name] AS 姓名,
MAX( CASE subject WHEN '语文' THEN score END) AS 语文,
MAX( CASE subject WHEN '数学' THEN score END) AS 数学,
MAX( CASE subject WHEN '英语' THEN score END) AS 英语
FROM
(select name,subject,
CASE WHEN score>=80 THEN '优' WHEN score<80 AND score >=60 THEN '良' WHEN score<60 THEN '差' END score
from student ) as a
GROUP BY [name]
--2005
select * from
(select name,subject,
CASE WHEN score>=80 THEN '优' WHEN score<80 AND score >=60 THEN '良' WHEN score<60 THEN '差' END score
from student) as a
pivot(max(score) for subject in(语文,数学,英语))b