MySQL转置

2021-11-14

字数统计: 555字 | 阅读时长≈ 2分

1.概述
2.实现方式
- 1.差异化类别较少情况
- 2.多类型，通用转置sql
3.感慨
4.致谢

1.概述

最近工作中，遇到合作方给的数据格式为多行存储的单人单次记录，为方便领导查看，需要将其转换为单行，这时候就需要用到强大的sql转置语句。

一般分为两种情况，差异化较少以及差异化类型特别多的情况；前一种可手动设置字段名，后一种则需要脚本自动化计算。

2.实现方式

1.差异化类别较少情况

# 转置并创建视图

create or replace
algorithm = UNDEFINED view `v_records` as
select
    `ttr`.`user_idcard` as `idcard`,
    max((case `ttr`.`type` when '1' then `ttr`.`value` else 0 end)) as `type1`,
    max((case `ttr`.`type` when '2' then `ttr`.`value` else 0 end)) as `type2`,
    max((case `ttr`.`type` when '3' then `ttr`.`value` else 0 end)) as `type3`,
    max((case `ttr`.`type` when '4' then `ttr`.`value` else 0 end)) as `type4`,
    max((case `ttr`.`type` when '5' then `ttr`.`value` else 0 end)) as `type5`,
    max((case `ttr`.`type` when '6' then `ttr`.`value` else 0 end)) as `type6`,
    ...
    cast(`ttr`.`create_time` as date) as `create_time`
from
    `tb_record` `ttr`
    where xxx = 'xxx'
group by
    `ttr`.`idcard`,
    cast(`ttr`.`create_time` as date)

2.多类型，通用转置sql

具体的sql说明见以下文本：

-- sql 太长必须设置
SET GLOBAL group_concat_max_len = (50*1024);
SET GLOBAL max_allowed_packet = (50*1024*1024);

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      -- max 函数在mysql5与8之间有区别，需要注意括号
      'max((case when ques_code = ''',
      -- ques_code 为差异化数据，作为转置依据
      ques_code,
      ''' then answer_name end)) as "',
      ques_code,   -- 数字或文字(ques_name)用引号包裹做字段名
      '"'
    )
  ) INTO @sql
FROM
  tb_table_name;

-- select @sql;

-- 拼接最终sql语句；idcard 为多行聚合的依据，可以是多个字段
SET @sql = CONCAT('SELECT idcard, create_time, ', @sql, ' FROM tb_table_name GROUP BY idcard');


select @sql;  -- 查看sql语句，当作打断点

PREPARE stmt FROM @sql;  -- sql语句
EXECUTE stmt;            -- 执行sql
DEALLOCATE PREPARE stmt; -- 释放

3.感慨

1.领导的需求才是第一生产力
2.SQL强大，满足大部分需求
3.自身对MySQL的理解还不够深入

4.致谢

本文作者： Linking
本文链接： https://linking.fun/2021/11/14/MySQL转置/

Table of Contents

1.概述

2.实现方式

1.差异化类别较少情况

2.多类型，通用转置sql

3.感慨

4.致谢