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

原始数据

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;

资源下载

参考