001將每個userid對應的多個科目的成績查出來，通過UNION ALL將結果集加起來。

變成

select userId,'語文' as subjectName,chinese_score as score from tb_score1
union all
select userId,'數學' as subjectName,math_score as score from tb_score1
union all
select userId,'英語' as subjectName,english_score as score from tb_score1
union all
select userId,'政治' as subjectName,politics_score as score from tb_score1;

002使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱。

-- 考察case表達式
select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成績 >=70 and 成<85 then 1 else 0 end) as '[85-70]',
sum(case when 成績>=60 and 成績<70 then 1 else 0 end) as '[70-60]',
sum(case when 成績<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.課程號=b.課程號 group by a.課程號,b.課程名稱;

003查詢出每門課程的及格人數和不及格人數。

-- 考察case表達式
select 課程號,
sum(case when 成績>=60 then 1 else 0 end) as 及格人數,
sum(case when 成績 <  60 then 1 else 0 end) as 不及格人數
from score group by 課程號;

004使用case…when…then 實現原本同一列下多行的不同內容作為多個字段，輸出對應內容。

轉化后

SELECT
	userid,
	SUM( CASE subjectName WHEN '語文' THEN score ELSE 0 END ) '語文',
	SUM( CASE subjectName WHEN '數學' THEN score ELSE 0 END ) '數學',
	SUM( CASE subjectName WHEN '英語' THEN score ELSE 0 END ) '英語',
	SUM( CASE subjectName WHEN '政治' THEN score ELSE 0 END ) '政治' 
FROM
	tb_score 
GROUP BY
	userid;

005使用SUM(IF()) 實現原本同一列下多行的不同內容作為多個字段，輸出對應內容。

SELECT
	userid,
	SUM( IF ( subjectName = '語文', score, 0 ) ) '語文',
	SUM( IF ( subjectName = '數學', score, 0 ) ) '數學',
	SUM( IF ( subjectName = '英語', score, 0 ) ) '英語',
	SUM( IF ( subjectName = '政治', score, 0 ) ) '政治' 
FROM
	tb_score 
GROUP BY
	userid;

006使用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行 。

MySQL提供了 group by with rollup 函數進行group by 字段的匯總，但是與order by 互斥的不能同時用。

SELECT
	IFNULL( userid, 'total' ) AS userid,
	SUM( IF ( subjectName = '語文', score, 0 ) ) '語文',
	SUM( IF ( subjectName = '數學', score, 0 ) ) '數學',
	SUM( IF ( subjectName = '英語', score, 0 ) ) '英語',
	SUM( IF ( subjectName = '政治', score, 0 ) ) '政治',
	SUM( IF ( subjectName = 'total', score, 0 ) ) AS 'total' 
FROM
	(
		SELECT
			userid,
			IFNULL( subjectName, 'total' ) AS subjectName,
			SUM( score ) AS score 
		FROM
			tb_score 
		GROUP BY userid, subjectName WITH ROLLUP 
	) AS a
GROUP BY userid
WITH ROLLUP;

007使用SUM(IF()) 生成列，直接生成匯總結果，不再利用子查詢。

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數學',score,0)) AS '數學',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid WITH ROLLUP;

008使用SUM(IF()) 生成列 + UNION 生成匯總行,并利用 IFNULL將匯總行標題顯示為 Total。

SELECT userid,
SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數學',score,0)) AS '數學',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid
UNION
SELECT 'total',SUM(IF(subjectName='語文',score,0)) AS '語文',
SUM(IF(subjectName='數學',score,0)) AS '數學',
SUM(IF(subjectName='英語',score,0)) AS '英語',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) FROM tb_score;

009group_concat()合并字段顯示如下內容。

SELECT userid,GROUP_CONCAT(`subjectName`,":",score)AS 成績 FROM tb_score GROUP BY userid;