精华内容
下载资源
问答
  • MySQL行转列函数

    万次阅读 2019-11-04 09:07:51
    原文链接: ...概述 好久没写SQL语句,今天看到问答中的一个问题,拿来...学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作。数据表里面数据如下图,使用姓名+课程作为联合主键(...

    原文链接:
    http://www.360doc.com/content/18/0525/20/14808334_757019563.shtml
    概述
    好久没写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”的值;

    关于效率

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

    还有其它更好的方法吗?

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

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

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

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

    有些需求可能不需要联合主键,因为一门课程可能允许学生考多次,取最好的一次成绩,或者取多次的平均成绩。
    最简单的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;
    
    展开全文
  • demo:语句:SELECT '行' id, '' product_nameUNIONSELECT id, product_name ... 5结果:行1icbc2测试测试314笔记本电脑语句:SELECT '行转列后' id, '' product_nameUNIONSELECT GROUP_CONCAT(id) id, GROUP_CONCA...

    demo:

    8e0518d1f0226edcebf8e690d204e18d.png

    语句:

    SELECT '行' id, '' product_name

    UNION

    SELECT id, product_name FROM `product` WHERE id < 5

    结果:

    1icbc

    2测试测试

    31

    4笔记本电脑

    e7f40fda432ab75e92deddee75f44a34.png

    语句:

    SELECT '行转列后' id, '' product_name

    UNION

    SELECT GROUP_CONCAT(id) id, GROUP_CONCAT(product_name) product_name FROM `product` WHERE id < 5

    结果:

    行转列后

    1,2,3,4icbc,测试测试,1,笔记本电脑

    bc4938bca1fd2d710562eaec6dbda43a.png

    语句:

    SELECT '行' id, '' product_name

    UNION

    SELECT id, product_name FROM `product` WHERE id < 5

    UNION

    SELECT '行转列后' id, '' product_name

    UNION

    SELECT GROUP_CONCAT(id) id, GROUP_CONCAT(product_name) product_name FROM `product` WHERE id < 5

    结果:

    1icbc

    2测试测试

    31

    4笔记本电脑

    行转列后

    1,2,3,4icbc,测试测试,1,笔记本电脑

    a216e7e330f346a9a5d11367806320d2.png

    展开全文
  • mysql动态行转列

    2016-11-04 14:13:22
    mysql动态行转列的例子
  • 行转列首先我们先创建一个表 表如下 CREATE TABLE test_hjsjy_1( `id` int(10) NOT NULL AUTO_INCREMENT, `user_name` varchar(20) DEFAULT NULL, `course` varchar(20) DEFAULT NULL, `score` float DEFAULT...

    行转列首先我们先创建一个表

    表如下
    在这里插入图片描述

    CREATE TABLE test_hjsjy_1(
    	`id` int(10) NOT NULL AUTO_INCREMENT,
       `user_name` varchar(20) DEFAULT NULL,
    		`course` varchar(20) DEFAULT NULL,
    		`score` float DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8;
    

    给表赋值

    INSERT INTO test_hjsjy_1(user_name,COURSE,score) VALUES
    ("老大","数学",56),
    ("老大","语文",35),
    ("老大","英语",87),
    ("老二","数学",36),
    ("老二","语文",78),
    ("老二","英语",52),
    ("老三","数学",72),
    ("老三","语文",91),
    ("老三","英语",59);
    

    行转列mysql语句

    SELECT user_name,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END) 数学,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END) 语文,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END) 英语
    from test_hjsjy_1 
    GROUP BY user_name;
    

    效果如下
    在这里插入图片描述

    这里大部分第一次学mysql 行转列地时候看到这个语句会很奇怪为啥要用MAX函数呢? 这个不是多余地吗?

    解答: 这并不是多余地,其实我们来测试一下我不加max看是什么效果

    SELECT user_name ,
        (CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
        (CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
       (CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
    FROM test_hjsjy_1
    GROUP BY user_name;
    

    看一下我们查询的效果
    在这里插入图片描述
    这里我们会发现我们老大老二老三的语文和英语成绩怎么没了都是0?
    我们再尝试看看把上面的查询语句中group by user_name 试着去掉

    SELECT user_name ,
        (CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
        (CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
       (CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
    FROM test_hjsjy_1
    

    再看看查询结果
    在这里插入图片描述
    聪明的人的现在知道为啥需要用max函数了,其实是因为group by分组,你没有max函数 他就会查询每个分组的第一个元组,所以max函数是不能少的,这个问题也启发我们,遇到这种问题我们可以通过调试sql,来解决我们遇到的疑惑。

    列转行举例首先我们需要创建一个表

    表如下
    在这里插入图片描述

    CREATE TABLE `test_hjsjy_2` (
     `id` int(10) NOT NULL AUTO_INCREMENT,
     `user_name` varchar(20) DEFAULT NULL,
     `chinese_score` float DEFAULT NULL,
     `math_score` float DEFAULT NULL,
     `english_score` float DEFAULT '0',
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    

    列转行表赋值

    insert into test_hjsjy_2(user_name, chinese_score, math_score, english_score) values
    ("老大", 34, 58, 58),
    ("老二", 45, 87, 45),
    ("老三", 76, 34, 89);
    

    列转行mysql语句

    select user_name, '语文' COURSE , chinese_score as SCORE from test_hjsjy_2
    union select user_name, '数学' COURSE, math_score as SCORE from test_hjsjy_2
    union select user_name, '英语' COURSE, english_score as SCORE from test_hjsjy_2
    order by user_name,course;
    

    表结果如下
    在这里插入图片描述
    这样我们也就实现了列转行。

    展开全文
  • 转行:利用max(case when then) max—聚合函数 取最大值 (case course when ‘语文’ then score else 0 end) —判断 as 语文—别名作为列名 SELECT `name`, MAX( CASE WHEN course='\u8bed\u6587' THEN ...
  • hive 行转列/转行 多行转一行/一行转多行 Mysql 创建表语句: CREATE TABLE student_score( id BIGINT PRIMARY key auto_increment, s_name VARCHAR(20) , s_sub VARCHAR(20), s_score INT ); insert into student_...

    hive 行转列/列转行 多行转一行/一行转多行

    Mysql

    创建表语句:

    CREATE TABLE student_score(
    id BIGINT PRIMARY key auto_increment,
    s_name VARCHAR(20) ,
    s_sub VARCHAR(20),
    s_score INT 
    );
    
    insert into student_score values(null,'张三','数学',90);
    insert into student_score values(null,'张三','语文',85);
    insert into student_score values(null,'张三','英语',92);
    insert into student_score values(null,'李四','数学',88);
    insert into student_score values(null,'李四','语文',91);
    insert into student_score values(null,'李四','英语',99);
    insert into student_score values(null,'王五','数学',100);
    insert into student_score values(null,'王五','语文',82);
    insert into student_score values(null,'王五','英语',88);
    
    
    select * from student_score;
    

    在这里插入图片描述

    行转列

    业务场景,按照上图看的话很难只看同学们的数学成绩或者语文成绩,所以要按照以下格式展示:

    s_name数学语文英语
    张三908592
    李四889199
    李四1008288

    思路:
    第一步可以将每一行根据学科s_sub分成数学,语文,英语三列 , sql语句:

    case  s_sub  when '数学' then s_score else 0 end 数学
    

    完整sql:

    select s_name,
    case s_sub when '数学' then s_score else 0 end 数学,
    case s_sub when '语文' then s_score else 0 end 语文,
    case s_sub when '英语' then s_score else 0 end 英语
    from student_score
    

    结果:
    在这里插入图片描述
    最后,需要按照s_name为维度group by聚合一下

    select s_name,
    sum(case s_sub when '数学' then s_score else 0 end) 数学,
    sum(case s_sub when '语文' then s_score else 0 end) 语文,
    max(case s_sub when '英语' then s_score else 0 end) 英语
    from student_score group by s_name
    

    最终结果:
    在这里插入图片描述
    如果不是int类型而是 varchar 那么上面是行不通的,因为sum里面不能跟字符串,需要用group_concat()函数替代sum,可先看多行转一行之后再看下方sql

    select s_name,
    GROUP_CONCAT(case s_sub when '数学' then  CAST(s_score AS char) else null end) 数学,
    GROUP_CONCAT(case s_sub when '语文' then  CAST(s_score AS char) else null end) 语文,
    GROUP_CONCAT(case s_sub when '英语' then  CAST(s_score AS char) else null end) 英语
    from student_score group by s_name
    

    多行转一行

    业务场景,以张三为例,将张三的三个成绩拼接成字符串 放到一个单元格中显示:

    '张三'    '数学:90,语文:85,英语92'
    

    上述 类似于

    select s_name, sum(s_score) from student_score group by s_name
    

    sum 就是一个聚合函数,只不过它是将所有的数字相加得到结果,根据上面业务场景,此时要用到一个函数 group_concat ,类似于sum 但是它的作用是将聚合后的数据进行字符拼接
    group_concat (a,b,c,… separator ’ ‘) 或者
    group_concat (a,b,c,…)
    group_concat 中的参数是可变参数,多个参数拼接,最后一个speparator xxx 是多行数据拼接后的分隔符,如果不写默认的分隔符是’,'逗号.并且记住要用group by 维度聚合,不写group by 会拼接所有的行
    完整的sql:

    select s_name,group_concat(s_sub,':',s_score separator '@') all_score from  student_score group by s_name
    

    结果:
    在这里插入图片描述

    列转行

    首先要用到上面行转列的数据

    create table student_score2 as 
    select s_name,
    sum(case s_sub when '数学' then s_score else 0 end) 数学,
    sum(case s_sub when '语文' then s_score else 0 end) 语文,
    max(case s_sub when '英语' then s_score else 0 end) 英语
    from student_score group by s_name
    

    在这里插入图片描述
    下面是实现列转行的sql

    SELECT
    	s_name,
    	'数学' AS s_sub,
    	数学 AS s_score
      FROM s_score
    UNION
    SELECT
    	s_name,
    	'语文' AS s_sub,
    	语文 AS s_score
      FROM s_score
    UNION
    SELECT
    	s_name,
    	'英语' AS s_sub,
    	英语 AS s_score
      FROM s_score
    

    结果
    在这里插入图片描述

    一行转多行

    一行转多行在关系型数据库中是有点儿矛盾的,因为关系型数据库设计原则就是单条数据不能再分,如果需要再分是外部(hive/java程序)分完之后进入关系型数据库的,虽然矛盾,但是也是可以实现的.
    创建表:

    create table student_score3 as
    select s_name,group_concat(s_sub,':',s_score separator '@') all_score 
    from  student_score group by s_name
    

    先sql再说思路:

    SELECT
     a.s_name,
     substring_index( substring_index( a.all_score, '@', b.id), '@',- 1 )
    FROM
     student_score3 a
     JOIN account b ON 
     b.id <= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );
    

    account表是作为辅助结构的提供1-n个自增长数字
    在这里插入图片描述
    查询结果:
    在这里插入图片描述

    思路:(根据sql思想一步步替换成可执行的sql)

    ①根据张三的一条数据来看:首先,把张三分成三条,三是all_score中想切分成的元素个数决定的

    name    index          all_score
    张三      1         数学:90@语文:85@英语:92
    张三      2         数学:90@语文:85@英语:92
    张三      3         数学:90@语文:85@英语:92
    

    上图sql思想是:

    select a.s_name, allscore from a join b on n <= all_score_arr.size(); n>=1
    --这样子可以根据n的个数join出n条数据.
    

    ②转化成下面我们想要的这样:

    name    index       score
    张三      1         数学:90
    张三      2         语文:85
    张三      3         英语:92
    

    如果把all_score看成一个数组的话,刚好score就是取的all_score_arr的第index个元素
    可以得出sql的思路:

    select a.s_name,all_score_arr[n] from a join b on n <= all_score_arr.size();
    

    如果把上面的sql转换成mysql可以执行的sql语句,就能完成多行转一行的目的.

    ③初步思路有了以后,先看mysql的函数
    先是函数 substring_index(str,sp,x)
    str是要切分的字符串,sp指分隔符,x是int类型通过下面sql

    select substring_index(all_score,'@',x) from student_score3;
    

    可以看规律 x 为1 结果是’数学:90’,x为2 结果是’数学:90@语文85’(从右往左取)
    x为-1 结果是’英语:90’,x为-2 结果是’语文:85@英语;92’(从左往右取)
    所以要用两个substring_index()函数来实现类似于将字符串all_score根据@切分成数组再根据all_score_arr索引取元素.
    substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )
    n= 1时 取的是’数学:90’
    n = 2时 取的是’语文:85’

    这就完成了模拟数组取元素
    所以all_score_arr[n] 可以用 substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )替代:

    select a.s_name,substring_index( substring_index( all_score, '@', n), '@',- 1 )
    from a join b on n <= all_score_arr.size();
    

    n代表着整数可以通过另外一个表的自增主键来替代,b.id就代表了从1-n的整数,得到下面sql:

    select a.s_name,substring_index( substring_index( all_score, '@', b.id), '@',- 1 )
    from a join b on b.id <= all_score_arr.size();
    

    将a与b与实际的表进行替换

    select a.s_name,substring_index(substring_index( all_score, '@', b.id), '@',- 1 )
    from student_score2 a join account b on b.id <= all_score_arr.size();
    

    all_score_arr.size()也就是元素的个数应该如何表示呢?
    可以通过(分隔符+1)来表示元素个数,
    length( a.all_score ) 是字符长度 length( replace( a.all_score, ‘@’, ‘’ ) ) 是去掉分隔符后的长度
    两者相减就得到分隔符数,分隔符数加1就是元素个数
    所以arr.size() 可以用 length( a.all_score ) - length( replace( a.all_score, ‘@’, ‘’ ) ) + 1 表示
    替换后得到最终结果:

     select a.s_name,substring_index( substring_index( a.all_score, '@', b.id+ 1 ), '@',- 1 ) 
     from a join b on b.id<= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );
    
    展开全文
  • 即一中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,如 value AT BT CT DT ET SQL...
  • NULL 博文链接:https://pmandy-163-com.iteye.com/blog/789326
  • MySQL---行转列

    千次阅读 2021-03-02 13:52:34
    1、SqlServer和Orcle中可以使用pivot行转列函数快速实现,而MySQL中没有 pivot: SELECT * FROM student PIVOT ( SUM(score) FOR subject IN (语文, 数学, 英语) ) 2、MySQL实现行转列 SELECT name, MAX( CASE ...
  • 1.原数据结构 第一个是test表 第二个是course表 2.想要的数据结构 3.编写的sql语句 select course.name,concat('分层班课程:',group_concat(test.score separator '、')) '课程' ...group ...
  • MySQL -- 行转列的两种方法

    万次阅读 2018-12-23 02:21:39
    MySQL中行转是很常用的,本文给出了两种行转列的方法。 两张表: 左表:product ,右表:cate SQL语句: 第一种方法:IF()语句 SELECT cid, MAX(IF(mon ='一月份',num,0)) '一月份', MAX(IF(mon ='二月份...
  • mysql行转列转行示例

    千次阅读 2021-01-28 06:52:53
    最近在开发过程中遇到问题,需要将数据库中一张表信息进行行转列操作,再将每(即每个字段)作为与其他表进行联表查询的字段进行显示。借此机会,在网上查阅了相关方法,现总结出一种比较简单易懂的方法备用。一、...
  • MYSQL实现行转列的三种方式

    千次阅读 2020-06-23 10:01:08
    姓名 语文 数学 英语 张三 78 88 98 李四 89 76 90 王五 89 56 89 解题 ​ 这是一道典型的行转列题目,给定表中行有学科,而目标表中是学科。 解法一:case when ​ 通过case when,可以较容易的实现将行转换成...
  • Mysql中行转列转行

    万次阅读 多人点赞 2018-08-14 11:54:39
    一、行转列 即将原本同一下多行的不同内容作为多个字段,输出对应内容。 建表语句 DROP TABLE IF EXISTS tb_score; CREATE TABLE tb_score( id INT(11) NOT NULL auto_increment, userid VARCHAR(20) NOT ...
  • MySQL行转列转行、连接字符串 concat、concat_ws、group_concat函数用法http://blog.csdn.net/aya19880214/article/details/41280893浅析MySQL中concat以及group_concat的使用...
  • Mysql递归及行转列,并优化

    千次阅读 2016-08-12 17:10:23
    创建递归函数,根据项目rootId 找到其所有子项目 最终返回 以“,”连接在一起的子项目id串,然后将这个传行转列
  • MySQL行转列实现和总结

    千次阅读 2018-07-06 14:57:40
    一、行转列实例1、准备数据CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB; INSERT INTO tb VALUES('张三','语文',74); INSERT INTO tb VALUES('张三','数学',83); INSERT ...
  • mysql 行转列 (带日期)

    千次阅读 2017-11-11 16:51:09
    从网上找了很多行转列的。基本都是2行的行转列。不带日期分组的。 借鉴了另一个哥们的文章,实现了自己想要的结果,写出来大家可以参考。以后自己遇到同样情况,也可以有个备份 借鉴的地址为 ...
  • mysql行转列(不固定)

    千次阅读 2019-11-25 11:20:52
    使用场景:由于题目的选项按存储在数据库中,并且选项数量不是固定的,如下图 使用sql语句如下 SELECT questionitem.questionId, IF(length(GROUP_CONCAT(questionitem.content))-length(replace(GROUP_CONCAT...
  • Mysql中行转列算法

    2012-12-24 14:28:20
    mysql行转列算法举例与分析,有益于对mysql语法学习
  • mysql 行转列转行实例详解

    千次阅读 2020-07-10 11:26:10
    这篇文章主要介绍了mysql 行转列转行实例详解的相关资料,需要的朋友可以参考下 mysql行转列转行语句不难,不做多余解释了,看语句时,从内往外一句一句剖析。
  • mysql行转列转换

    千次阅读 2018-07-24 10:56:55
    其中最为头痛的就是多行,动态的列行转换。最近在研究这些行里转换,还是从最为简单的行列转换开始。 sql 脚本 -- 创建表 学生表 CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号'...
  • mysql行转列,使用json_objectagg实现

    千次阅读 2020-10-01 22:29:49
    因为mysql没有oracle的pivot行转列函数,所以在网上搜了很久 最终大部分答案都是这种: 十几篇博文全部一样的,抄的图片都模糊了,真的服 这都在sql指定列名了,怎么用,这算哪门子动态法 一搜一大把,全是这种,看...
  • mysql行转列(多行转一

    千次阅读 2018-07-16 11:31:00
    场景  比如说一个订单对应多条数据,当状态(status)=1的时候, 数量(num)=25,当状态(status)=2的时候, 数量(num)=45,现在想用一条sql记录下不同状态对应的数量为多少,如下图所示: ...
  • mysql行转列汇总数据

    2020-06-03 21:19:10
    这道题要用到汇总,所以我们要用 sum函数,一个人对应一条数据,所以我们要对name进行group by分组: SELECT s.name, SUM(CASE WHEN s.category='语文' THEN s.score ELSE 0 END) AS '语文', SUM(CASE WHEN s....
  • mysql 逗号分隔符 行转列转行

    千次阅读 2021-01-19 00:37:14
    行转列,直接使用 group_concat 分组函数select id,group_concat(name) from aa group by id;由于这个是分组函数,还可以用distinct去重select id,group_concat(distinct name) from aa group by id;还可以指定分割...
  • MySQL 动态 行转列值转换列名)

    万次阅读 多人点赞 2018-04-02 16:34:39
    那么需要这样的结果就要进行行转列来操作了。 静态行转列 Select st.stuid, st.stunm, MAX ( CASE c.coursenm WHEN '大学语文' THEN s.scores ELSE 0 END ) '大学语文' , MAX ( CASE c....
  • MySQL中将一以逗号分隔的值行转列

    万次阅读 2019-01-26 16:46:08
    即一中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求: 得到所有的不重复的值,如 value ...
  • mysql 单表行转列,两表级联行转列

    千次阅读 2019-06-10 16:55:52
    参考文章:... 一、单表行转列 数据表: 转行:利用max(case when then) max---聚合函数取最大值 (casecoursewhen'语文'thenscoreelse0end)---判断 as 语文---别名作为列名 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 227,876
精华内容 91,150
关键字:

mysql行转列函数

mysql 订阅
友情链接: Form1.rar