Mysql行转列输出【实战】

本文最后更新于：2021年6月15日 晚上

在很多场景下，需要将一行一行的数据转换成一列数据。比如每个人的每个科目都有一个分数，并分别对应一个分数，但是为了更加直观地看到一个人的所有科目成绩，所以需要将每个科目成绩由行转换成列。

原始数据

id stuid name subject score
1 110 xiaoA 语文 99
2 110 xiaoA 数学 88
3 112 xiaoB 语文 69
4 112 xiaoB 数学 100

行数据转换成列数据之后

stuid name 语文 数学
110 xiaoA 99 88
112 xiaoB 69 100 
1
2
3
4
SELECT stuid, `name`,
  MAX(CASE `subject` WHEN 'C001' THEN score ELSE 0 END) as '语文',
  MAX(CASE `subject` WHEN 'C002' THEN score ELSE 0 END) as '数学'
FROM `sys_user` GROUP BY stuid;

以下通过两个在工作中遇到的实例，来使用 Mysql 的行转列。

实例

实例一

网站进行推广，每个推广员都有一个唯一 ID 即 pid，现在要统计每个推广员的推广数据；需要统计推广过来的新增用户，以及新增用户的充值以及消费数据。这里简化了数据，便于理解。

数据说明：

  • create_time： 数据生成时间
  • pid： 推广员唯一ID
  • type： 数据类型
    • 1： 新增用户
    • 2： 新增充值
    • 3： 新增消费
  • value： 对应的数据值

需求结果样式：

SQL 查询： 

1
2
3
4
5
6
7
SELECT
	create_time,
MAX(CASE type WHEN 1 THEN value ELSE 0 END) '新增用户',
MAX(CASE type WHEN 2 THEN value ELSE 0 END) '新增充值',
MAX(CASE type WHEN 3 THEN value ELSE 0 END) '新增订阅'
FROM
	wings_companion_analytics GROUP BY create_time

实例二

统计网站单本书的购买次数以及购买金额

数据说明：

  • id： 主键
  • bookId： 书的 ID
  • task_id： 数据类型
    • 706： 购买次数
    • 707： 购买金额
  • time： 数据生成时间
  • value： 对应的数据值

需求结果样式：

SQL 查询： 

1
2
3
4
5
6
7
8
SELECT bookId as '作品ID',
  DATE_FORMAT(time,'%Y-%m-%d') as '日期',
  MAX(CASE task_id WHEN 706 THEN `value` ELSE 0 END) AS '购买次数',
  MAX(CASE task_id WHEN 707 THEN `value` ELSE 0 END) AS '购买金额'
from `t_task_result_day`
WHERE task_id IN (706, 707)
GROUP BY time, bookId
ORDER BY bookId, time;

资源下载

参考