sql转置两种实现方式记录

最新推荐文章于 2024-05-12 11:30:35 发布

LinkingGe

最新推荐文章于 2024-05-12 11:30:35 发布

阅读量3.8k

点赞数 1

分类专栏： MySql 文章标签： sql 数据库 database 转置

本文链接：https://blog.csdn.net/jwl965278523/article/details/121928401

版权

MySql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

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

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(
      -- group_concat 可合并多选问题
      'group_concat((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; -- 释放