目录
提示：本次基于mysql8.0版本进行测试
前言
对于离线分析报表种，这种需要行转换为列的过程其实还是非常常见的，本文中主要介绍了mysql8.0版本2种方法进行行转化列，其他的方法还有很多得自己去专研一下了。
1. 数据准备
这里导入一批订单数据作为行转列的测试数据，脚本可直接执行（mysql 环境）
脚本：
# 1.创建一个订单表
create table if not exists orders
(
sn varchar(20) primary key comment '订单编号',
goods varchar(60) comment '商品名称',
unit int comment '单价',
count int comment '数量',
je double(7,2) comment '销售金额',
time date comment '销售日期',
month varchar(20) comment '销售月份',
uname varchar(20) comment '销售人员'
) comment '订单表';
# 2.初始化表数据
insert into orders(sn,goods,unit,count,time,uname)
values
('sn1000','华为meta60',6999,1,STR_TO_DATE('2024/6/7', '%Y/%m/%d'),'小红')
,('sn1001', '华为平板meta pad3', 3998,2,STR_TO_DATE('2024/1/8', '%Y/%m/%d'),'小张')
,('sn1002', '苹果Ipone15', 8698, 2, STR_TO_DATE('2024/12/9', '%Y/%m/%d'),'小美')
,('sn1003', '索尼照相机', 10240, 1, STR_TO_DATE('2024/2/10', '%Y/%m/%d'),'鹏哥')
,('sn1004', '华为Watch3', 2998, 2, STR_TO_DATE('2024/4/11', '%Y/%m/%d'),'鹏哥')
,('sn1005', '华为Watch4', 3999, 3, STR_TO_DATE('2024/2/24', '%Y/%m/%d'),'鹏哥')
,('sn1006', '索尼照相机', 10240, 1, STR_TO_DATE('2024/9/25', '%Y/%m/%d'),'小张')
,('sn1007', '苹果Ipone15', 8698, 1, STR_TO_DATE('2024/2/26', '%Y/%m/%d'),'小张')
,('sn1008', '华为平板meta pad3', 3998,2,STR_TO_DATE('2024/6/27', '%Y/%m/%d'),'小张')
,('sn1009', '华为meta60', 6999, 3, STR_TO_DATE('2024/11/28', '%Y/%m/%d'),'小张')
,('sn1010', '华为meta60', 6999, 1, STR_TO_DATE('2024/7/29', '%Y/%m/%d'),'小美')
,('sn1011', '华为pura 70', 5400, 1, STR_TO_DATE('2024/11/1', '%Y/%m/%d'),'鹏哥')
,('sn1012', '华为meta60', 6999, 2, STR_TO_DATE('2024/11/22', '%Y/%m/%d'),'小胖')
,('sn1013', '华为Watch3', 2998, 2, STR_TO_DATE('2024/12/3', '%Y/%m/%d'),'小胖')
,('sn1014', '华为meta60', 6999, 4, STR_TO_DATE('2024/8/5', '%Y/%m/%d'),'小胖')
,('sn1015', '华为meta60', 6999, 4, STR_TO_DATE('2024/10/10', '%Y/%m/%d'),'小胖')
,('sn1016', 'oppo Find x7', 4898, 4, STR_TO_DATE('2024/3/6', '%Y/%m/%d'),'小红')
,('sn1017', '华为Watch4', 3999, 1, STR_TO_DATE('2024/3/7', '%Y/%m/%d'),'小张')
,('sn1018', '华为navo 10', 3556, 1, STR_TO_DATE('2024/4/8', '%Y/%m/%d'),'小张')
,('sn1019', '华为navo 11', 3950, 1, STR_TO_DATE('2024/4/9', '%Y/%m/%d'),'小张')
,('sn1020', '华为meta60', 6999, 1, STR_TO_DATE('2024/3/10', '%Y/%m/%d'),'小胖')
,('sn1021', '华为平板meta pad3', 3998,1,STR_TO_DATE('2024/3/11', '%Y/%m/%d'),'小胖')
,('sn1022', '华为非法大师meta60', 19988,2,STR_TO_DATE('2024/8/12', '%Y/%m/%d'),'小张')
,('sn1023', '索尼照相机', 10240, 1, STR_TO_DATE('2024/3/13', '%Y/%m/%d'),'小美')
,('sn1024', '华为pura 70', 5400, 1, STR_TO_DATE('2024/12/14', '%Y/%m/%d'),'小美')
,('sn1025', 'oppo Find x7', 4898, 1, STR_TO_DATE('2024/3/15', '%Y/%m/%d'),'小美')
,('sn1026', '华为meta60', 6999, 2, STR_TO_DATE('2024/7/16', '%Y/%m/%d'),'鹏哥')
,('sn1027', 'oppo Find x7', 4898, 2, STR_TO_DATE('2024/3/17', '%Y/%m/%d'),'小张')
,('sn1028', '华为meta60', 6999, 1, STR_TO_DATE('2024/9/18', '%Y/%m/%d'),'小美');
# 3.更新销售金额，月份
update orders set
je=unit*count ,month=DATE_FORMAT(time,'%Y%m');
数据展示：
2. 用法和技巧
2.1 行转列+拼接
行转列+拼接：将一行数据转换为一列，并且按照指定分隔符进行拼接起来，形成一个新的列。具有代表的函数是：group_concat()函数
语法：
group_concat([distinct] 要转列字段 [order by asc/des 排序字段] [Separator ‘分隔符’])
说明：
distinct # 表示去重
要转列的字段 # 多个字段使用逗号“,”分割
order by # 可以进行排序
Separator # 每行之间的分隔符，默认为逗号“，”分割
案例：统计每个销售员，销售的商品种类多个商品之间用分号";"隔开，并去重。
效果展示
脚本：
select
uname,
group_concat(distinct goods separator " ;") as goods_list
from orders d
group by uname注：虽然这个函数在mysql8.0版本中非常好用，但是一般数据比较大时，就不要使用，该函数能转换拼接的范围有限。
2.2 行转列+聚合
在mysql中PIVOT 子句不能用，之前是准备用他来做该需求的，那么我们只能换一种思路了，利用条件语句 ，聚合函数，高级分组来得到下面的效果。
案例：统计1~12月份，每种商品的销售额，（要求每个月份显示1列，共12列)并进行汇总。
效果展示：
脚本：
select
IFNULL(mc,'总计') AS 商品名称,
sum(if(month='202401',je,0) ) as 1月,
sum(if(month='202402',je,0)) as 2月,
sum(if(month='202403',je,0)) as 3月,
sum(if(month='202404',je,0)) as 4月,
sum(if(month='202405',je,0)) as 5月,
sum(if(month='202406',je,0)) as 6月,
sum(if(month='202407',je,0)) as 7月,
sum(if(month='202408',je,0)) as 8月,
sum(if(month='202409',je,0)) as 9月,
sum(if(month='202410',je,0)) as 10月,
sum(if(month='202411',je,0)) as 11月,
sum(if(month='202412',je,0)) as 12月,
sum(if(month is null,je,0)) as 总计
from (
select
goods as mc,
month,
sum(je) as je
from orders d
group by goods,month WITH rollup
having goods is not null
) as t
group by mc WITH rollup注：语句中使用里with rollup(高级分组），他的目的是进行小计和汇总的，这里的：
group by goods,month WITH rollup 代表按照goods 进行所有商品进行汇总，并且在每种商品里，按照month月份进行小计的汇总。
3. 总结
行转列的过程还是很消耗资源的，所有大表建议不要使用group_concat函数，尽量使用第二种方法，使用一些条件语句+聚合函数来进行行的转化。
187
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
