MySQL行转列函数[通俗易懂]

MySQL行转列函数[通俗易懂]原文链接:http://www.360doc.com/content/18/0525/20/14808334_757019563.shtml概述好久没写SQL语句,今天看到问答中的一个问题,拿来研究一下。问题链接:关于Mysql的分级输出问题情景简介学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作列。数据表里面数据如下图,使用姓名+课程作为联合主键(…

大家好,又见面了,我是你们的朋友全栈君。

原文链接:
http://www.360doc.com/content/18/0525/20/14808334_757019563.shtml
概述
好久没写SQL语句,今天看到问答中的一个问题,拿来研究一下。

问题链接:关于Mysql 的分级输出问题

情景简介
学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作列。数据表里面数据如下图,使用姓名+课程作为联合主键(有些需求可能不需要联合主键)。本文以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”的值;

关于效率

不知道有什么好的生成模拟数据的方法或工具,麻烦小伙伴推荐一下,抽空我做一下对比;

还有其它更好的方法吗?

本文使用的几种方法应该都有优化的空间,特别是使用存储过程的话会更加灵活,功能更强大;

本文的分级只是给出一种思路,分级的方法如果学生的成绩相差较小的话将失去意义;

如果小伙伴有更好的方法,还请不吝赐教,感激不尽!

有些需求可能不需要联合主键

有些需求可能不需要联合主键,因为一门课程可能允许学生考多次,取最好的一次成绩,或者取多次的平均成绩。
最简单的case when

SELECT
	COUNT(*) AS num,
	(
		CASE PAY_TYPE
		WHEN '0' THEN
			'微信支付'
		WHEN '1' THEN
			'支付宝支付'
		WHEN '2' THEN
			'无感支付'
		WHEN '3' THEN
			'银联'
		WHEN '4' THEN
			'白名单'
		WHEN '5' THEN
			'月卡'
		END
	) AS type
FROM
	t_park_order
GROUP BY
	PAY_TYPE;
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/131025.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(6)


相关推荐

  • 解决nginx: [error] open() "/usr/local/nginx/logs/nginx.pid" failed错误

    解决nginx: [error] open() "/usr/local/nginx/logs/nginx.pid" failed错误

  • sqlpro studio 2021激活码[最新免费获取]

    (sqlpro studio 2021激活码)这是一篇idea技术相关文章,由全栈君为大家提供,主要知识点是关于2021JetBrains全家桶永久激活码的内容IntelliJ2021最新激活注册码,破解教程可免费永久激活,亲测有效,下面是详细链接哦~https://javaforall.cn/100143.html1STL5S9V8F-eyJsaWN…

  • 完整测试流程详解

    完整测试流程详解一:需求分析(了解熟悉业务,分析需求测试点)1.确认功能(业务功能,辅助功能,数据约束,易用性需求,编辑约束,参数需求,权限需求,性能约束)2.场景分析(考虑场景调用者和系统内部各个场景之间联系)3.挖掘隐性需求(测试计划测试用例执行测试测试报告bug清单…

  • 常见的几种矩阵分解方式

    常见的几种矩阵分解方式1.三角分解(LU分解)矩阵的LU分解是将一个矩阵分解为一个下三角矩阵与上三角矩阵的乘积。本质上,LU分解是高斯消元的一种表达方式。首先,对矩阵A通过初等行变换将其变为一个上三角矩阵。对于学习过线性代数的同学来说,这个过程应该很熟悉,线性代数考试中求行列式求逆一般都是通过这种方式来求解。然后,将原始矩阵A变为上三角矩阵的过程,对应的变换矩阵为一个下三角矩阵。这中间的过程,就是Doolittleal

  • mysql审计开启–两种方法

    mysql审计开启–两种方法

  • M3U8在线播放

    M3U8在线播放M3U8在线播放前言一、思路二、代码框架1.移动端适配2.改变M3U8地址3.设置videojs参数4.增加快进等功能写在最后前言当我们在网上愉快观影的时候,难免会遇到“M3U8格式”的视频。聪明的你应该也发现了,它是没办法直接播放的。它其实只是一个索引文件,根据它找到相应的.ts文件再进行播放。而这样做的好处,大概就是做多码率适配,保证视频播放的流畅性。有感兴趣的小伙伴可以参看这里—>M3U8文件格式。我今天要干的事情呢,就是解决当我们找到一个M3U8地址之后如何方便的播放它~一

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号