Mysql之行与列的多种转换实现方式_Mysql

mysql行与列的多种转换

首先准备一张表

create table cj
(
    name    varchar(32),
    subject varchar(32),
    result  int(10)
);

# 插入数据
insert into cj
values ('张三', '语文', 80),
       ('张三', '数学', 90),
       ('张三', '物理', 85),
       ('李四', '语文', 85),
       ('李四', '数学', 92),
       ('李四', '物理', 82);

一、行转列

第一步，先将科目分类好：

select name,
       case when subject='语文' then result else 0 end as 语文,
       case when subject='数学' then result else 0 end as 数学,
       case when subject='物理' then result else 0 end as 物理
from cj;

第二步：将上面的结果看做一张表，从表中找出每一个新字段的最大值，对name进行分组

select t.name,
       max(t.语文) 语文,
       max(t.数学) 数学,
       max(t.物理) 物理
from (select name,
       case when subject='语文' then result else 0 end as 语文,
       case when subject='数学' then result else 0 end as 数学,
       case when subject='物理' then result else 0 end as 物理
from cj) t
group by t.name;

案例二：查询用户安装app的情况

create table app
(
    id  int,
    app varchar(32)
);

insert into app(id, app)
values (1, '微信'),
       (2, '快手'),
       (3, 'qq'),
       (4, '抖音'),
       (5, '美团'),
       (6, '饿了么'),
       (7, '支付宝'),
       (8, '拼多多'),
       (9, '高德地图');

create table app_install
(
    uid int,
    app varchar(32)
);

insert into app_install(uid, app)
values (1, '微信'),
       (1, '美团'),
       (2, '支付宝'),
       (2, '高德地图'),
       (3, '拼多多');

select uid,
       case when app = '微信' then 1 else 0 end as 'wx',
       case when app = '快手' then 1 else 0 end as 'ks',
       case when app = 'qq' then 1 else 0 end as 'qq',
       case when app = '抖音' then 1 else 0 end as 'dy',
       case when app = '美团' then 1 else 0 end as 'mt',
       case when app = '饿了么' then 1 else 0 end as 'elm',
       case when app = '支付宝' then 1 else 0 end as 'zfb',
       case when app = '拼多多' then 1 else 0 end as 'pdd',
       case when app = '高德地图' then 1 else 0 end as 'gd'
from app_install;

select t.uid,
       case when max(t.wx) then '已安装' else '未安装' end as 'wx',
       case when max(t.ks) then '已安装' else '未安装' end as 'ks',
       case when max(t.qq) then '已安装' else '未安装' end as 'qq',
       case when max(t.dy) then '已安装' else '未安装' end as 'dy',
       case when max(t.mt) then '已安装' else '未安装' end as 'mt',
       case when max(t.elm) then '已安装' else '未安装' end as 'eml',
       case when max(t.zfb) then '已安装' else '未安装' end as 'zfb',
       case when max(t.pdd) then '已安装' else '未安装' end as 'pdd',
       case when max(t.gd) then '已安装' else '未安装' end as 'gd'
from  (select uid,
       case when app = '微信' then 1 else 0 end as 'wx',
       case when app = '快手' then 1 else 0 end as 'ks',
       case when app = 'qq' then 1 else 0 end as 'qq',
       case when app = '抖音' then 1 else 0 end as 'dy',
       case when app = '美团' then 1 else 0 end as 'mt',
       case when app = '饿了么' then 1 else 0 end as 'elm',
       case when app = '支付宝' then 1 else 0 end as 'zfb',
       case when app = '拼多多' then 1 else 0 end as 'pdd',
       case when app = '高德地图' then 1 else 0 end as 'gd'
from app_install) t
group by t.uid;

连表比子查询要好

二、列转行

建表

create table cj2
(
    name varchar(32),
    `语文` int(10),
    `数学` int(10),
    `物理` int(10)
);


# 插入数据
insert into cj2 values ('张三',80,90,90),('李四',85,92,92);

原表：

select name,'语文' cource,语文 result
from cj2
union all
select name,'数学' cource,数学 result
from cj2
union all
select name,'物理' cource,物理 result
from cj2;

# 查询后按照结果排序
select *
from (select name,'语文' cource,语文 result
from cj2
union all
select name,'数学' cource,数学 result
from cj2
union all
select name,'物理' cource,物理 result
from cj2) t
order by t.name;

三、多列转一行

将科目与分数排在一列

select name,group_concat(subject,':',result) 成绩
from cj
group by name;

四、一行转多列

将上表还原

# 建表
create table cj3
(
    name varchar(32),
    `成绩` varchar(50)
);

# 插入数据
insert into cj3
values ('张三', '语文:80,数学:90,物理:85'),
       ('李四', '语文:85,数学:92,物理:82');

select name,
       case
           when locate('语文', 成绩) > 0 then substring_index(substring_index(成绩, '语文:', -1), ',', 1)
           else 0 end as 语文,
       case
           when locate('数学', 成绩) > 0 then substring_index(substring_index(成绩, '数学:', -1), ',', 1)
           else 0 end as 数学,
       case
           when locate('物理', 成绩) > 0 then substring_index(substring_index(成绩, '物理:', -1), ',', 1)
           else 0 end as 物理
from cj3;

select t1.name, '语文' cource, t1.语文 result
from (select name,
             case
                 when locate('语文', 成绩) > 0 then substring_index(substring_index(成绩, '语文:', -1), ',', 1)
                 else 0 end as 语文
      from cj3) t1
union all
select t2.name, '数学' cource, t2.数学 result
from (select name,
             case
                 when locate('数学', 成绩) > 0 then substring_index(substring_index(成绩, '数学:', -1), ',', 1)
                 else 0 end as 数学
      from cj3) t2
union all
select t3.name, '物理' cource, t3.物理 result
from (select name,
             case
                 when locate('物理', 成绩) > 0 then substring_index(substring_index(成绩, '物理:', -1), ',', 1)
                 else 0 end as 物理
      from cj3) t3;

select *
from (select t1.name, '语文' cource, t1.语文 result
from (select name,
             case
                 when locate('语文', 成绩) > 0 then substring_index(substring_index(成绩, '语文:', -1), ',', 1)
                 else 0 end as 语文
      from cj3) t1
union all
select t2.name, '数学' cource, t2.数学 result
from (select name,
             case
                 when locate('数学', 成绩) > 0 then substring_index(substring_index(成绩, '数学:', -1), ',', 1)
                 else 0 end as 数学
      from cj3) t2
union all
select t3.name, '物理' cource, t3.物理 result
from (select name,
             case
                 when locate('物理', 成绩) > 0 then substring_index(substring_index(成绩, '物理:', -1), ',', 1)
                 else 0 end as 物理
      from cj3) t3) t
order by t.name;

五、行转列的其他案例

准备一张result表

行转列

# 查询1000号学生四门科目的成绩
select studentno,
       case when subjectno = 1 then studentresult else 0 end as 高等数学1,
       case when subjectno = 2 then studentresult else 0 end as 高等数学2,
       case when subjectno = 3 then studentresult else 0 end as java编程,
       case when subjectno = 4 then studentresult else 0 end as hadoop理论
from result
where studentno = 1000;

# 简化
select studentno, max(高等数学1) math1, max(高等数学2) math2, max(java编程) java, max(hadoop理论) hadoop
from (select studentno,
             case when subjectno = 1 then studentresult else 0 end as 高等数学1,
             case when subjectno = 2 then studentresult else 0 end as 高等数学2,
             case when subjectno = 3 then studentresult else 0 end as java编程,
             case when subjectno = 4 then studentresult else 0 end as hadoop理论
      from result
      where studentno = 1000) t;