MySQL如何实现⾏转列SQL
转⾃：微点阅读（www.weidianyuedu）
问题链接：关于Mysql 的分级输出问题
情景简介
学校⾥⾯记录成绩，每个⼈的选课不⼀样,⽽且以后会添加课程，所以不需要把所有课程当作列。数据表⾥⾯数据如下图，使⽤姓名+课程作为联合主键（有些需求可能不需要联合主键）。本⽂以MySQL为基础，其他数据库会有些许语法不同。
数据库表数据：
处理后的结果（⾏转列）：
⽅法⼀：
这⾥可以使⽤Max，也可以使⽤Sum；
注意第⼆张图，当有学⽣的某科成绩缺失的时候，输出结果为Null;
[sql] view plain copy
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语句解决这个问题:
[sql] view plain copy
MAX(
CASE CNAME
WHEN "JAVA" THEN
SCORE
ELSE
END
) JAVA,
MAX(
CASE CNAME
WHEN "mysql" THEN
SCORE
ELSE
END
) mysql
FROM
stdscore
GROUP BY
SNAME;
⽅法⼆：
[sql] view plain copy
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
⽅法三：
[sql] view plain copy
DROP PROCEDURE
IF EXISTS sp_score;
DELIMITER &&
CREATE PROCEDURE sp_score ()
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 = (
mysql存储过程使用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 ",
"\"",
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;
[sql] view plain copy
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 "普通"
)
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;
⽅法⼆：
[sql] view plain copy
SELECT DISTINCT  a.sname,
(SELECT (
CASE
WHEN SCORE - (select avg(SCORE) from stdscore where CNAME="JAVA") > 20 THEN "优秀"

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。