行转列
一、找出每个同学的各科成绩
SELECT student_id,student_name
MAX(CASE WHEN subject_name = '高等数学'THEN score ELSE 0 END) AS'高等数学',
MAX(CASE WHEN subject_name = '机械原理'THEN score ELSE 0 END) AS'机械原理',
MAX(CASE WHEN subject_name = '概率论'THEN score ELSE 0 END) AS'概率论',
MAX(CASE WHEN subject_name = '几何学'THEN score ELSE 0 END) AS'几何学',
MAX(CASE WHEN subject_name = '计算机导论'THEN score ELSE 0 END) AS'计算机导论'
FROM score
GROUP BY
student_id,student_name
ORDER BY
student_id,student_name;
二、找出男生和女生每门课的平均成绩
# NULL不计入算数,但是0会计入
SELECT student_id,student_name
AVG(CASE WHEN subject_name = '高等数学'THEN score ELSE NULL END) AS'高等数学',
AVG(CASE WHEN subject_name = '机械原理'THEN score ELSE NULL END) AS'机械原理',
AVG(CASE WHEN subject_name = '概率论'THEN score ELSE NULL END) AS'概率论',
AVG(CASE WHEN subject_name = '几何学'THEN score ELSE NULL END) AS'几何学',
AVG(CASE WHEN subject_name = '计算机导论'THEN score ELSE NULL END) AS'计算机导论'
FROM score
GROUP BY
gender
ORDER BY
gender;
方法二、子查询
SELECT b.gender
MAX(CASE WHEN subject_name = '高等数学'THEN score ELSE 0 END) AS'高等数学',
MAX(CASE WHEN subject_name = '机械原理'THEN score ELSE 0 END) AS'机械原理',
MAX(CASE WHEN subject_name = '概率论'THEN score ELSE 0 END) AS'概率论',
MAX(CASE WHEN subject_name = '几何学'THEN score ELSE 0 END) AS'几何学',
MAX(CASE WHEN subject_name = '计算机导论'THEN score ELSE 0 END) AS'计算机导论'
FROM (
SELECT a.gender,a.subject_name,avg(score) avg_score
FROM score a
GROUP BY a.gender,a.subject_name
) b
GROUP BY
gender
ORDER BY
gender;
三、将同一记录中的多个词合并成一个字段以逗号间隔
使用GROUP_CONCAT()函数
SELECT student_id,student_name
GROUP_CONCAT(subject_name),GROUP_CONCAT(score)
FROM score a
GROUP BY
a.student_id, a.student_name;