将MySQL中按行记录的数据转换成按列记录

背景

在开发场景中，现系统部分表结构是这样的：

id	type	value
1	A	10
1	B	20
1	C	30
2	A	40
2	B	50
2	C	60

而我想要的表结构是这样的：

id	A	B	C
1	10	20	30
2	40	50	60

因为后续要和其他表关联，所以在SQL查询时就需要进行行列转换。

实现方案

不难想到这里可以用分组+聚合函数来实现这一需求，曾以为有更好的解决方案，ChatGPT并没有给出，看来并没有。

利用max函数 + case when 语句

这是我自己想到的一个，实际上可以更简短。ai助手首先给出的也是这条。

SELECT MAX(CASE WHEN type = 'A' THEN value END) AS A,
       MAX(CASE WHEN type = 'B' THEN value END) AS B,
       MAX(CASE WHEN type = 'C' THEN value END) AS C
FROM your_table
GROUP BY id;

利用sum函数 + if函数

这是ai助手给的，在数字上是一样的，但是在别的类型上有一点问题，后面说明。

SELECT id,
       SUM(IF(type = 'A', value, 0)) AS A,
       SUM(IF(type = 'B', value, 0)) AS B,
       SUM(IF(type = 'C', value, 0)) AS C
FROM your_table
GROUP BY id;

其他

目测sum+case、max+if应该也是可以的，而且if更短更易读，例如：

SELECT id,
       max(IF(type = 'A', value, 0)) AS A,
       max(IF(type = 'B', value, 0)) AS B,
       max(IF(type = 'C', value, 0)) AS C
FROM your_table
GROUP BY id;

简单测试后发现，上面这条max if语句可以达到目标效果，而sum case、sum if的组合产生的是全数字的结果，对比发现其是截取了左起非数字字符前的所有数字。

例如我想要的效果是（省略表头）：

23^张三	2022-12-11
41^李四	2023-7-21

sum的效果则是：

23	2022
41	2023

推测是sum函数对各类型数据做了截取，只转化计算了其中一部分数据。

声明：本文使用八爪鱼rpa工具从gitee自动搬运本人原创（或摘录，会备注出处）博客，如版式错乱请评论私信，如情况紧急或久未回复请致邮 xkm.0jiejie0@qq.com 并备注原委；引用本人笔记的链接正常情况下均可访问，如打不开请查看该链接末尾的笔记标题（右击链接文本，点击复制链接地址，在文本编辑工具粘贴查看，也可在搜索框粘贴后直接编辑然后搜索），在本人博客手动搜索该标题即可；如遇任何问题，或有更佳方案，欢迎与我沟通！

秒客网

将MySQL中按行记录的数据转换成按列记录