MySQL高频面试题：一维表转二维表

SELECT stu.*,c_id,score
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id;

SELECT stu.s_id,stu.s_name,
		SUM(if(c_id='01',score,0)) '01',
		SUM(if(c_id='02',score,0)) '02',
        SUM(if(c_id='03',score,0)) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

SELECT stu.s_id,stu.s_name,
		SUM(CASE WHEN c_id='01' THEN score ELSE 0 END) '01',
		SUM(CASE WHEN c_id='02' THEN score ELSE 0 END) '02',
        SUM(CASE WHEN c_id='03' THEN score ELSE 0 END) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

SELECT stu.s_id,stu.s_name,
		SUM((c_id='01')*score) '01',
        SUM((c_id='02')*score) '02',
        SUM((c_id='03')*score) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

SELECT stu.s_id,
		SUM((c_id='01')*score) '语文',
        SUM((c_id='02')*score) '数学',
        SUM((c_id='03')*score) '英语',
		AVG(score) '平均分'
FROM  stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id
ORDER BY AVG(score) DESC;

SELECT sc.c_id,c_name,max(score) 最高分,min(score) 最低分,AVG(score) 平均分,
		SUM(score>=60 and score<70)/(SELECT COUNT(s_id) FROM stu) 及格率,
        SUM(score>=70 and score<80)/(SELECT COUNT(s_id) FROM stu) 中等率,
        SUM(score>=80 and score<90)/(SELECT COUNT(s_id) FROM stu) 优良率,
        SUM(score>=90)/(SELECT COUNT(s_id) FROM stu) 优秀率
FROM sc 
LEFT JOIN co ON sc.c_id=co.c_id
GROUP BY sc.c_id;

SELECT sc.c_id, c_name,
       SUM(score BETWEEN 0 AND 60)/COUNT(sc.c_id) AS '[0-60]所占百分比',
       SUM(score BETWEEN 60 AND 70)/COUNT(sc.c_id) AS '[60-70]所占百分比',
       SUM(score BETWEEN 70 AND 85)/COUNT(sc.c_id) AS '[70-85]所占百分比',
       SUM(score BETWEEN 85 AND 100)/COUNT(sc.c_id) AS '[85-100]所占百分比'
FROM sc 
LEFT JOIN co ON sc.c_id=co.c_id
GROUP BY sc.c_id;