27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT m.* ,n.总分 ,n.平均分 FROM
( SELECT * FROM abc PIVOT( MAX(分数) FOR subjectName IN (语文,数学,英语)) a) m ,
( SELECT studentID,SUM(分数) 总分,CAST(AVG(分数 * 1.0) AS DECIMAL(18, 2)) 平均分 FROM abc GROUP BY studentID) n
WHERE m.studentID = n.studentID order by studentID
select studentID,studentName,
max(case subjectName when '语文' then 分数 else 0 end)语文,
max(case subjectName when '数学' then 分数 else 0 end)数学,
max(case subjectName when '英语' then 分数 else 0 end)英语,
sum(分数)总分,
cast(avg(分数*1.0) as decimal(18,2))平均分
from abc
group by studentID,studentName order by studentID
WITH abc(studentID,studentName,subjectName,分数) AS (
SELECT '2001','张三','数学',90 UNION ALL
SELECT '2001','张三','语文',80 UNION ALL
SELECT '2001','张三','英语',70 UNION ALL
SELECT '2002','李四','数学',80 UNION ALL
SELECT '2002','李四','语文',70 UNION ALL
SELECT '2002','李四','英语',60 UNION ALL
SELECT '2003','刘五','数学',60 UNION ALL
SELECT '2003','刘五','语文',50 UNION ALL
SELECT '2003','刘五','英语',40
)
select studentName,
max(case subjectName when '语文' then 分数 else 0 end)语文,
max(case subjectName when '数学' then 分数 else 0 end)数学,
max(case subjectName when '物理' then 分数 else 0 end)物理,
sum(分数)总分,
cast(avg(分数*1.0) as decimal(18,2))平均分
from abc
group by studentName