001將每個(gè)userid對(duì)應(yīng)的多個(gè)科目的成績(jī)查出來(lái)，通過(guò)UNION ALL將結(jié)果集加起來(lái)。

變成

select userId,'語(yǔ)文' as subjectName,chinese_score as score from tb_score1
union all
select userId,'數(shù)學(xué)' as subjectName,math_score as score from tb_score1
union all
select userId,'英語(yǔ)' 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]來(lái)統(tǒng)計(jì)各科成績(jī)，分別統(tǒng)計(jì)：各分?jǐn)?shù)段人數(shù)，課程號(hào)和課程名稱(chēng)。

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

003查詢出每門(mén)課程的及格人數(shù)和不及格人數(shù)。

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

004使用case…when…then 實(shí)現(xiàn)原本同一列下多行的不同內(nèi)容作為多個(gè)字段，輸出對(duì)應(yīng)內(nèi)容。

轉(zhuǎn)化后

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

005使用SUM(IF()) 實(shí)現(xiàn)原本同一列下多行的不同內(nèi)容作為多個(gè)字段，輸出對(duì)應(yīng)內(nèi)容。

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

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

MySQL提供了 group by with rollup 函數(shù)進(jìn)行g(shù)roup by 字段的匯總，但是與order by 互斥的不能同時(shí)用。

SELECT
	IFNULL( userid, 'total' ) AS userid,
	SUM( IF ( subjectName = '語(yǔ)文', score, 0 ) ) '語(yǔ)文',
	SUM( IF ( subjectName = '數(shù)學(xué)', score, 0 ) ) '數(shù)學(xué)',
	SUM( IF ( subjectName = '英語(yǔ)', score, 0 ) ) '英語(yǔ)',
	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()) 生成列，直接生成匯總結(jié)果，不再利用子查詢。

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

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

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

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

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