MySQL如何實現行轉列分級輸出？

概述

好久沒寫SQL語句，今天看到問答中的一個問題，拿來研究一下。

問題鏈接：關於Mysql 的分級輸出問題

情景簡介

學校裡面記錄成績，每個人的選課不一樣,而且以後會添加課程，所以不需要把所有課程當作列。數據表裡面數據如下圖，使用姓名+課程作為聯合主鍵（有些需求可能不需要聯合主鍵）。本文以MySQL為基礎，其他數據庫會有些許語法不同。

數據庫表數據：

處理後的結果（行轉列）：

方法一：

這裡可以使用Max，也可以使用Sum；

注意第二張圖，當有學生的某科成績缺失的時候，輸出結果為Null;

 

SELECT
	SNAME,
	MAX(
		CASE CNAME
		WHEN 'JAVA' THEN
			SCORE
		END
	) JAVA,
	MAX(
		CASE CNAME
		WHEN 'mysql' THEN
			SCORE
		END
	) mysql
FROM
	stdscore
GROUP BY
	SNAME;

 

可以在第一個Case中加入Else語句解決這個問題:

 

SELECT
	SNAME,
	MAX(
		CASE CNAME
		WHEN 'JAVA' THEN
			SCORE
		ELSE
			0
		END
	) JAVA,
	MAX(
		CASE CNAME
		WHEN 'mysql' THEN
			SCORE
		ELSE
			0
		END
	) mysql
FROM
	stdscore
GROUP BY
	SNAME;

方法二：

 

 

SELECT DISTINCT  a.sname,
(SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',
(SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'
FROM stdscore a

方法三：

 

DROP PROCEDURE
IF EXISTS sp_score;
DELIMITER &&

CREATE PROCEDURE sp_score ()
BEGIN
	#課程名稱
	DECLARE
		cname_n VARCHAR (20) ; #所有課程數量
		DECLARE
			count INT ; #計數器
			DECLARE
				i INT DEFAULT 0 ; #拼接SQL字符串
			SET @s = 'SELECT sname' ;
			SET count = (
				SELECT
					COUNT(DISTINCT cname)
				FROM
					stdscore
			) ;
			WHILE i < count DO


			SET cname_n = (
				SELECT
					cname
				FROM
					stdscore
				GROUP BY CNAME 
				LIMIT i,
				1
			) ;
			SET @s = CONCAT(
				@s,
				', SUM(CASE cname WHEN ',
				'\'',
				cname_n,
				'\'',
				' THEN score ELSE 0 END)',
				' AS ',
				'\'',
				cname_n,
				'\''
			) ;
			SET i = i + 1 ;
			END
			WHILE ;
			SET @s = CONCAT(
				@s,
				' FROM stdscore GROUP BY sname'
			) ; #用於調試
			#SELECT @s;
			PREPARE stmt
			FROM
				@s ; EXECUTE stmt ;
			END&&

CALL sp_score () ;

 

 

處理後的結果（行轉列）分級輸出：

 

方法一：

 

這裡可以使用Max，也可以使用Sum；

注意第二張圖，當有學生的某科成績缺失的時候，輸出結果為Null;

 

SELECT
	SNAME,
	MAX(
		CASE CNAME
		WHEN 'JAVA' THEN
			(
				CASE
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
					'優秀'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
					'良好'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
					'普通'
				ELSE
					'較差'
				END
			)
		END
	) JAVA,
	MAX(
		CASE CNAME
		WHEN 'mysql' THEN
			(
				CASE
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
					'優秀'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
					'良好'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
					'普通'
				ELSE
					'較差'
				END
			)
		END
	) mysql
FROM
	stdscore
GROUP BY
	SNAME;

 

方法二：

SELECT DISTINCT  a.sname,
(SELECT (
				CASE
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
					'優秀'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
					'良好'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
					'普通'
				ELSE
					'較差'
				END
			) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',
(SELECT (
				CASE
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN
					'優秀'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN
					'良好'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN
					'普通'
				ELSE
					'較差'
				END
			) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'
FROM stdscore a

方法三：

DROP PROCEDURE
IF EXISTS sp_score;
DELIMITER &&

CREATE PROCEDURE sp_score ()
BEGIN
	#課程名稱
	DECLARE
		cname_n VARCHAR (20) ; #所有課程數量
		DECLARE
			count INT ; #計數器
			DECLARE
				i INT DEFAULT 0 ; #拼接SQL字符串
			SET @s = 'SELECT sname' ;
			SET count = (
				SELECT
					COUNT(DISTINCT cname)
				FROM
					stdscore
			) ;
			WHILE i < count DO


			SET cname_n = (
				SELECT
					cname
				FROM
					stdscore
        GROUP BY CNAME 
				LIMIT i, 1
			) ;
			SET @s = CONCAT(
				@s,
				', MAX(CASE cname WHEN ',
				'\'',
				cname_n,
				'\'',
				' THEN (
				CASE
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 20 THEN
					\'優秀\'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 10 THEN
					\'良好\'
				WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') >= 0 THEN
					\'普通\'
				ELSE
					\'較差\'
				END
			) END)',
				' AS ',
				'\'',
				cname_n,
				'\''
			) ;
			SET i = i + 1 ;
			END
			WHILE ;
			SET @s = CONCAT(
				@s,
				' FROM stdscore GROUP BY sname'
			) ; 
			#用於調試
			#SELECT @s;
			PREPARE stmt
			FROM
				@s ; EXECUTE stmt ;
			END&&


CALL sp_score ();

幾種方法比較分析

第一種使用了分組，對每個課程分別處理。
第二種方法使用了表連接。
第三種使用了存儲過程，實際上可以是第一種或第二種方法的動態化，先計算出所有課程的數量，然後對每個分組進行課程查詢。這種方法的一個最大的好處是當新增了一門課程時，SQL語句不需要重寫。

小結

關於行轉列和列轉行

這個概念似乎容易弄混，有人把行轉列理解為列轉行，有人把列轉行理解為行轉列；

這裡做個定義：

行轉列：把表中特定列（如本文中的：CNAME）的數據去重後做為列名（如查詢結果行中的“JAVA，mysql”，處理後是做為列名輸出）；

列轉行：可以說是行轉列的反轉，把表中特定列（如本文處理結果中的列名“JAVA，mysql”）做為每一行數據對應列“CNAME”的值；

 

關於效率

不知道有什麼好的生成模擬數據的方法或工具，麻煩小伙伴推薦一下，抽空我做一下對比；

 

還有其它更好的方法嗎？

本文使用的幾種方法應該都有優化的空間，特別是使用存儲過程的話會更加靈活，功能更強大；

本文的分級只是給出一種思路，分級的方法如果學生的成績相差較小的話將失去意義；

如果小伙伴有更好的方法，還請不吝賜教，感激不盡！

 

有些需求可能不需要聯合主鍵

有些需求可能不需要聯合主鍵，因為一門課程可能允許學生考多次，取最好的一次成績，或者取多次的平均成績。