MySQL 行列转换

本文最后更新于：2020年12月30日 凌晨

场景

面试的时候遇到的一个问题，之前没有碰到过这种场景，所以却是无论如何都回答不了呢！然而本着遇到的坑跌倒过一次就够了的理念，回来时吾辈稍微 Google 了一下这个问题，结果便在此记录一下好啦

行转列

指的是将数据行根据状态区分为不同的列，主要应用场景应该是统计报表吧

例如下面这个 exam 表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table if exists exam;
create table exam (
  name    varchar(20) not null
  comment '姓名',
  subject varchar(20) not null
  comment '考试科目',
  score   int         null
  comment '考试成绩'
)
  comment '考试记录';

insert into exam values ('琉璃', '语文', 90);
insert into exam values ('琉璃', '英语', 85);
insert into exam values ('楚轩', '数学', 100);
insert into exam values ('楚轩', '物理', 100);
insert into exam values ('张三', '化学', 40);
insert into exam values ('李四', '生物', 100);

直接查询会是下面这个样子

姓名 科目 分数
琉璃 语文 90
琉璃 英语 85
楚轩 数学 100
楚轩 物理 100
张三 化学 40
李四 生物 100

然而需要的结果却是

姓名 语文 数学 英语 物理 化学 生物
张三 0 0 0 0 40 0
李四 0 0 0 100 0 0
楚轩 0 100 0 100 0 0
琉璃 90 0 85 0 0 0

大致的实现思路是判断 subject 的值，如果等于 转换列 的值，就将之设置为该 转换列 的值。（此处的 转换列 指的是根据 subject 的值查询的新列）

目前网络上能找到的方法有下面两种

使用 if 实现行转列

1
2
3
4
5
6
7
8
9
10
select
  name                              as '姓名',
  max(if(subject = '语文', score, 0)) as '语文',
  max(if(subject = '数学', score, 0)) as '数学',
  max(if(subject = '英语', score, 0)) as '英语',
  max(if(subject = '物理', score, 0)) as '物理',
  max(if(subject = '化学', score, 0)) as '化学',
  max(if(subject = '生物', score, 0)) as '生物'
from exam
group by name;

优点：简单方便，即便是将几列合并也可以简单做到。例如我们想要统计主科/副科的总分

1
2
3
4
5
6
select
  name                                                                  as '姓名',
  sum(if(subject = '语文' or subject = '数学' or subject = '英语', score, 0)) as '主科',
  sum(if(subject = '物理' or subject = '化学' or subject = '生物', score, 0)) as '副科'
from exam
group by name;

查询结果

姓名 主科 副科
张三 0 40
李四 0 100
楚轩 100 100
琉璃 250 0

或者简单的实现小计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
  -- 这里的 ifnull 其实是为了让最后一行的统计不为 null
  ifnull(name, 'total')             as '姓名',
  max(if(subject = '语文', score, 0)) as '语文',
  max(if(subject = '数学', score, 0)) as '数学',
  max(if(subject = '英语', score, 0)) as '英语',
  max(if(subject = '物理', score, 0)) as '物理',
  max(if(subject = '化学', score, 0)) as '化学',
  max(if(subject = '生物', score, 0)) as '生物',
  -- 统计每一行数据
  sum(score)                        as total
from exam
-- 按照 name 进行分组并进行小计
group by name with rollup;

查询结果

姓名 语文 数学 英语 物理 化学 生物 total
张三 0 0 0 0 40 0 40
李四 0 0 0 0 0 100 100
楚轩 0 100 0 100 0 0 200
琉璃 90 0 85 0 0 0 250
total 90 100 85 100 40 100 590

使用 case when 实现行转列

1
2
3
4
5
6
7
8
9
10
select
  name as '姓名',
  max(case subject when '语文' then score else 0 end) as '语文',
  max(case subject when '数学' then score else 0 end) as '数学',
  max(case subject when '英语' then score else 0 end) as '英语',
  max(case subject when '物理' then score else 0 end) as '物理',
  max(case subject when '化学' then score else 0 end) as '化学',
  max(case subject when '生物' then score else 0 end) as '生物'
from exam
group by name;

优点：相比于 if 更加灵活，可以对每个 转换列 的值进行单独的处理。例如我们想要统计主科/副科的总分，并设置计算语文/数学时增加一半，而英语的分数则忽略不计

感觉这个优势相当的小，当然如果用到的话却是无需多言的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  name     as '姓名',
  sum(case subject
      when '语文'
        then score * 1.5
      when '数学'
        then score * 1.5
      when '英语'
        then 0
      else 0
      end) as '主科',
  sum(case subject
      when '物理'
        then score
      when '化学'
        then score
      when '生物'
        then score
      else 0
      end) as '副科'
from exam
group by name;

查询结果

姓名 主科 副科
张三 0.0 40
李四 0.0 100
楚轩 150.0 100
琉璃 247.5 0

使用子查询实现行转列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select
  name,
  if(language != 'null', language, 0)       as '语文',
  if(mathematics != 'null', mathematics, 0) as '数学',
  if(english != 'null', english, 0)         as '英语',
  if(physical != 'null', physical, 0)       as '物理',
  if(chemistry != 'null', chemistry, 0)     as '化学',
  if(biological != 'null', biological, 0)   as '生物'
from (
       select
         e.name,
         (select e1.score from exam e1 where subject = '语文' and e1.name = e.name limit 1) as language,
         (select e1.score from exam e1 where subject = '数学' and e1.name = e.name limit 1) as mathematics,
         (select e1.score from exam e1 where subject = '英语' and e1.name = e.name limit 1) as english,
         (select e1.score from exam e1 where subject = '物理' and e1.name = e.name limit 1) as physical,
         (select e1.score from exam e1 where subject = '化学' and e1.name = e.name limit 1) as chemistry,
         (select e1.score from exam e1 where subject = '生物' and e1.name = e.name limit 1) as biological
       from exam e
       group by name
     ) temp;

优点：使用起来最灵活，但代码量也是最大的。可以对每一个列的多条/单条数据进行单独的处理，不需要必须使用统计函数（sum/avg/max/min/count）。例如上面就是如果查到了多条数据就直接取第一条，当然也可以对第一条数据做后续处理。

使用 group_concat 简单的行连接

并非是真正的行转列，实际上只是把不同字段的数据 连接 了起来

1
2
3
4
5
select
  name                              as '姓名',
  group_concat(subject, ' ', score) as '成绩单'
from exam
group by name;

查询结果

姓名 成绩单
张三 化学 40
李四 生物 100
楚轩 数学 100,物理 100
琉璃 语文 75,语文 90,英语 85

列转行

将类似的列按照某种规则变成一列，并生成等同倍数的行。

我们需要将上面行转列得到的表转换回来，例如下面的 exam_score 表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create table exam_score (
  name        varchar(20) not null
  comment '姓名',
  language    int         not null
  comment '语文',
  mathematics int         not null
  comment '数学',
  english     int         not null
  comment '英语',
  physical    int         not null
  comment '物理',
  chemistry   int         not null
  comment '化学',
  biological  int         not null
  comment '生物'
)
  comment '考试成绩';
insert into exam_score (name, language, mathematics, english, physical, chemistry, biological)
values ('张三', 0, 0, 0, 0, 40, 0);
insert into exam_score (name, language, mathematics, english, physical, chemistry, biological)
values ('李四', 0, 0, 0, 0, 0, 100);
insert into exam_score (name, language, mathematics, english, physical, chemistry, biological)
values ('楚轩', 0, 100, 0, 100, 0, 0);
insert into exam_score (name, language, mathematics, english, physical, chemistry, biological)
values ('琉璃', 90, 0, 85, 0, 0, 0);

直接查询结果是

name language mathematics english physical chemistry biological
张三 0 0 0 0 40 0
李四 0 0 0 0 0 100
楚轩 0 100 0 100 0 0
琉璃 90 0 85 0 0 0

然而我们需要得到

姓名 科目 分数
琉璃 语文 90
琉璃 英语 85
楚轩 数学 100
楚轩 物理 100
张三 化学 40
李四 生物 100

使用 union all 联合查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
select
  name,
  '语文'     as 'subject',
  language as score
from exam_score
where language != 0
union all
select
  name,
  '数学'        as 'subject',
  mathematics as score
from exam_score
where mathematics != 0
union all
select
  name,
  '英语'    as 'subject',
  english as score
from exam_score
where english != 0
union all
select
  name,
  '物理'     as 'subject',
  physical as score
from exam_score
where physical != 0
union all
select
  name,
  '化学'      as 'subject',
  chemistry as score
from exam_score
where chemistry != 0
union all
select
  name,
  '生物'       as 'subject',
  biological as score
from exam_score
where biological != 0;

唔，好长的 sql 语句，这还只是 6 个 转换列，如果有更多的话恐怕。。。

总结

sql 行转列的问题

sql 的技巧确实很多，然而相比之下 sql 只是一门 结构化查询语言，并不算是真正的编程语言呢！行转列/列转行这些需求放到真正的编程语言中是很容易处理的，下面演示使用 js 的实现

使用 JavaScript 实现行转列

假设有下面这样一个 json 数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
[
  {
    "name": "琉璃",
    "subject": "语文",
    "score": 75
  },
  {
    "name": "琉璃",
    "subject": "语文",
    "score": 90
  },
  {
    "name": "琉璃",
    "subject": "英语",
    "score": 85
  },
  {
    "name": "楚轩",
    "subject": "数学",
    "score": 100
  },
  {
    "name": "楚轩",
    "subject": "物理",
    "score": 100
  },
  {
    "name": "张三",
    "subject": "化学",
    "score": 40
  },
  {
    "name": "李四",
    "subject": "生物",
    "score": 100
  }
]

转换方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/**
 * 行转列
 * @param {Array} arr 需要进行行转列的数组
 * @returns {Array} 行转列得到的数组
 */
function rowToCol(arr) {
  /**
   * js 数组按照某个条件进行分组
   * 注：分组完成后会得到一个二维数组，并且顺序会被打乱
   * 时间复杂度为 2On
   * @param {Function} {fn} 元素分组的方法，默认使用 {@link JSON.stringify()}
   * @returns {Array} 新的数组
   */
  Array.prototype.groupBy = function (fn = (item) => JSON.stringify(item)) {
    // 将元素按照分组条件进行分组得到一个 条件 -> 数组 的对象
    const obj = {}
    this.forEach((item) => {
      const name = fn(item)
      // 如果已经有这个键了就直接追加, 否则先将之赋值为 [] 再追加元素
      ;(obj[name] || (obj[name] = [])).push(item)
    })
    // 将对象转换为数组
    return Object.keys(obj).map((key) => obj[key])
  }

  /**
   * js 的数组去重方法
   * @param {Function} {fn} 唯一标识元素的方法，默认使用 {@link JSON.stringify()}
   * @returns {Array} 进行去重操作之后得到的新的数组 (原数组并未改变)
   */
  Array.prototype.uniqueBy = function (fn = (item) => JSON.stringify(item)) {
    const obj = {}
    return this.filter(function (item) {
      return obj.hasOwnProperty(fn(item)) ? false : (obj[fn(item)] = true)
    })
  }

  /**
   * 获取所有的科目 -> 分数映射表
   * 看起来函数有点奇怪，但实际上只是一个闭包函数而已
   * @returns {Object} 所有的科目 -> 分数映射表的拷贝
   */
  const subjectMap = ((obj) => () => Object.assign({}, obj))(
    arr
      .map((row) => row.subject)
      .uniqueBy()
      .reduce((res, subject) => {
        res[subject] = 0
        return res
      }, {}),
  )
  return arr
    .groupBy((row) => row.name)
    .map((arr) =>
      arr
        .uniqueBy((row) => row.subject)
        .reduce((res, temp) => {
          res = Object.assign(subjectMap(), res)
          res.name = temp.name
          res[temp.subject] = temp.score
          return res
        }, {}),
    )
}

看起来好像更长了？但实际上 groupBy()/uniqueBy() 都是通用的函数，所以实际代码应该不超过 20 行。转换后的数据如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
[
  {
    "语文": 75,
    "英语": 85,
    "数学": 0,
    "物理": 0,
    "化学": 0,
    "生物": 0,
    "name": "琉璃"
  },
  {
    "语文": 0,
    "英语": 0,
    "数学": 100,
    "物理": 100,
    "化学": 0,
    "生物": 0,
    "name": "楚轩"
  },
  {
    "语文": 0,
    "英语": 0,
    "数学": 0,
    "物理": 0,
    "化学": 40,
    "生物": 0,
    "name": "张三"
  },
  {
    "语文": 0,
    "英语": 0,
    "数学": 0,
    "物理": 0,
    "化学": 0,
    "生物": 100,
    "name": "李四"
  }
]

使用 JavaScript 实现列转行

那么，如何转换回来呢？转换回来的话却是简单许多了呢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
 * 列转行
 * @param {Array} arr 需要进行列转行的数组
 * @returns {Array} 列转行得到的数组
 */
function colToRow(arr) {
  // 定义好需要进行合并列的数组
  var cols = ['语文', '英语', '数学', '物理', '化学', '生物']
  return arr.flatMap((row) =>
    cols
      .map((subject) => ({
        name: row.name,
        subject: subject,
        score: row[subject],
      }))
      .filter((newRow) => newRow.score != 0),
  )
}

那么，关于 MySQL 行列转换的问题就到这里啦

其他 > MySQL
#sql
MySQL 行列转换
https://blog.rxliuli.com/p/5c0cf314a3974a9996241b9cc5296c17/
作者
rxliuli
发布于
2020年4月18日
许可协议