mysql 行转列分级输出_MySQL如何实现行转列分级输出？_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”的值；
關于效率
不知道有什么好的生成模擬數據的方法或工具，麻煩小伙伴推薦一下，抽空我做一下對比；
還有其它更好的方法嗎？
本文使用的幾種方法應該都有優化的空間，特別是使用存儲過程的話會更加靈活，功能更強大；
本文的分級只是給出一種思路，分級的方法如果學生的成績相差較小的話將失去意義；
如果小伙伴有更好的方法，還請不吝賜教，感激不盡！
有些需求可能不需要聯合主鍵
有些需求可能不需要聯合主鍵，因為一門課程可能允許學生考多次，取最好的一次成績，或者取多次的平均成績。
本文原創發布php中文網，轉載請注明出處，感謝您的尊重！
創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎總結
以上是生活随笔為你收集整理的mysql 行转列分级输出_MySQL如何实现行转列分级输出？_MySQL的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： P 社《燃灯者联盟》现已发售，登陆 PC
- 下一篇： java 组件化_(原创)搭建一个组件化