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”的值;

 

關於效率

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

 

還有其它更好的方法嗎?

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

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

如果小夥伴有更好的方法,還請不吝賜教,感激不盡!

 

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

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

 

You May Also Like