社区首页 >专栏 >SQL行转列、列转行

SQL行转列、列转行

全栈程序员站长

发布于 2022-07-21 07:32:49

1.7K00

代码可运行

文章被收录于专栏：全栈程序员必看全栈程序员必看

运行总次数：0

代码可运行

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

SQL行转列、列转行

这个主题还是比较常见的，行转列主要适用于对数据作聚合统计，如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。

一、整理测试数据

create table wyc_test(
	id int(32) not null auto_increment,
	name varchar(80) default null,
	date date default null,
	scount int(32),
	primary key (id)
);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (1,'小说','2013-09-01',10000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (2,'微信','2013-09-01',20000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (3,'小说','2013-09-02',30000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (4,'微信','2013-09-02',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (5,'小说','2013-09-03',31000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (6,'微信','2013-09-03',36000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (7,'小说','2013-09-04',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (8,'微信','2013-09-04',38000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (9,'小说','2013-09-01',80000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (10,'微信','2013-09-01',70000);

二、行转列

主要思路是分组后使用case进行条件判断处理

#行转列
select 
    a.date,
    sum(case a.name
        when '小说' then a.scount
        else 0
    end) 'sum_小说',
    max(case a.name
        when '小说' then a.scount
        else 0
    end) 'max_小说',
    sum(case a.name
        when '微信' then a.scount
        else 0
    end) '微信',
    max(case a.name
        when '小说' then a.scount
        else 0
    end) 'max_微信'
from
    wyc_test a
group by date;

结果：

三、列转行

主要思路也是分组后使用case

#列转行
select 
    a.date,
    concat('小说:',
            cast(sum(case a.name
                    when '小说' then a.scount
                    else 0
                end)
                as char),
            '微信',
            cast(sum(case a.name
                    when '微信' then a.scount
                    else 0
                end)
                as char)) as 'str'
from
    wyc_test a
group by a.date;
#列转行
#1.使用mysql提供的函数分组
select a.date,group_concat(a.name,'总量：', a.scount) from wyc_test a group by a.date,a.name;
#2.使用mysql提供的函数分组
select a.date,a.name, group_concat(a.name, '总量：', a.scount) from wyc_test a group by a.date,a.name;
#3.普通group结合字符串拼接
SELECT 
    a.date,
    concat('小说总量：',
            cast(sum(case a.name
                    when '小说' then a.scount
                    else 0
                end)
                as char)) as '小说',
    concat('微信总量：',
            cast(sum(case a.name
                    when '微信' then a.scount
                    else 0
                end)
                as char)) as '微信'
from
    wyc_test a
group by a.date;

结果：

四、列转行详解 1.1、初始测试数据表结构：TEST_TB_GRADE2 Sql代码 create table TEST_TB_GRADE2 ( ID NUMBER(10) not null, USER_NAME VARCHAR2(20 CHAR), CN_SCORE FLOAT, MATH_SCORE FLOAT, EN_SCORE FLOAT ) 初始数据如下图：

1.2、如果需要实现如下的查询效果图：

这就是最常见的列转行，主要原理是利用SQL里面的union，具体的sql语句如下： Sql代码 select user_name, ‘语文’ COURSE , CN_SCORE as SCORE from test_tb_grade2 union select user_name, ‘数学’ COURSE, MATH_SCORE as SCORE from test_tb_grade2 union select user_name, ‘英语’ COURSE, EN_SCORE as SCORE from test_tb_grade2 order by user_name,COURSE 也可以利用【 insert all into … select 】来实现，首先需要先建一个表TEST_TB_GRADE3： Sql代码 create table TEST_TB_GRADE3 ( USER_NAME VARCHAR2(20 CHAR), COURSE VARCHAR2(20 CHAR), SCORE FLOAT ) 再执行下面的sql： Sql代码 insert all into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, ‘语文’, CN_SCORE) into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, ‘数学’, MATH_SCORE) into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, ‘英语’, EN_SCORE) select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2; commit;

别忘记commit操作，然后再查询TEST_TB_GRADE3，发现表中的数据就是列转成行了。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/124752.html原文链接：https://javaforall.cn

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022年4月4，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法