SELECT student_name, SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文', SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学', SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语', SUM(CASE `subject` WHEN '特长加分' THEN score ELSE 0 END) as '特长加分' FROM t_gaokao_score GROUP BY student_name;
SELECT student_name, SUM(IF(`subject`='语文',score,0)) as '语文', SUM(IF(`subject`='数学',score,0)) as '数学', SUM(IF(`subject`='英语',score,0)) as '英语', SUM(IF(`subject`='特长加分',score,0)) as '特长加分' FROM t_gaokao_score GROUP BY student_name;
SELECT IFNULL(student_name,'总数') AS student_name, SUM(IF(`subject`='语文',score,0)) AS '语文', SUM(IF(`subject`='数学',score,0)) AS '数学', SUM(IF(`subject`='英语',score,0)) AS '英语', SUM(IF(`subject`='特长加分',score,0)) AS '特长加分', SUM(score) AS '总数' FROM t_gaokao_score GROUP BY student_name WITH ROLLUP;
SELECT student_name, MAX( CASE subject WHEN '语文' THEN ( CASE WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > 20 THEN '优秀' WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > 10 THEN '良好' WHEN score - (select avg(score) from t_gaokao_score where subject='语文') >= 0 THEN '普通' ELSE '差' END ) END ) as '语文', MAX( CASE subject WHEN '数学' THEN ( CASE WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > 20 THEN '优秀' WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > 10 THEN '良好' WHEN score - (select avg(score) from t_gaokao_score where subject='数学') >= 0 THEN '普通' ELSE '差' END ) END ) as '数学', MAX( CASE subject WHEN '英语' THEN ( CASE WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > 20 THEN '优秀' WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > 10 THEN '良好' WHEN score - (select avg(score) from t_gaokao_score where subject='英语') >= 0 THEN '普通' ELSE '差' END ) END ) as '英语', SUM(score) as '总分', (CASE WHEN SUM(score) > 430 THEN '重点大学' WHEN SUM(score) > 400 THEN '一本' WHEN SUM(score) > 350 THEN '二本' ELSE '工地搬砖' END ) as '结果' FROM t_gaokao_score GROUP BY student_name ORDER BY SUM(score) desc;