MySQL 如何实现行转列分级输出

最新推荐文章于 2025-04-18 11:44:50 发布

原创最新推荐文章于 2025-04-18 11:44:50 发布 · 177 阅读

0 ·

CC 4.0 BY-SA版权

本文探讨了使用SQL处理学生成绩数据的多种方法，包括行转列操作及成绩分级，通过案例展示了Max、Sum函数及存储过程的应用。

概述

好久没写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 ) mysqlFROM stdscoreGROUP 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 ) mysqlFROM stdscoreGROUP 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 PROCEDUREIF 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 ) mysqlFROM stdscoreGROUP 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 PROCEDUREIF 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 ();