今天接到一个群友的需求,有一张表的数据如图 1,他希望能通过 SQL 查询出图 2 的结果。
图 1 原始数据
图 2 输出的结果
先来分析图 1 是怎么变成图 2,图1 中的 tag1
、tag2
、tag3
三个字段都存在 NULL
值,且NULL
值无处不在,而图2 里面的NULL
只出现在这几个字段的末尾。这个就类似于 Excel 里面的操作,把 NULL
所在的单元格删了,下方的单元格往上移,如果下方单元格的值仍是 NULL
,则继续往下找,直到找到了非 NULL
值来补全这个单元格的内容。
那用 SQL 怎么表示呢?
有一个思路:把每一列去掉 NULL
后单独拎出来作为一张独立的表,这个表只有两个字段,一个是序号,另一个是去 NULL
后的值。比如 tag1
列变成 t1 表,tag2
列变成 t2 表,tag3
列变成 t3 表。
t1 表的数值如下:
id tag
------ --------
1 aaa1
2 aaa2
3 aaa3
t2 表的数值如下:
id tag
------ --------
1 bbb1
2 bbb2
3 bbb3
4 bbb4
t3 表的数值如下:
id tag
------ --------
1 ccc1
2 ccc2
3 ccc3
4 ccc4
5 ccc5
通过以下 SQL 就可以实现图 2 的效果。
SELECT
t3.id,
t1.tag AS tag1,
t2.tag AS tag2,
t3.tag AS tag3
FROM
t3
LEFT JOIN t2
ON t3.id = t2.id
LEFT JOIN t1
ON t1.id = t3.id
但是,这种实现方式有比较大的局限性,你必须知道哪个表的数据最多,然后用这张表左连接其它表。
一个比较灵活的做法是对原表的数据做列转行,最后再通过行转列实现图2 的输出。具体的实现看下面的 SQL(我偷懒了,直接把原数据通过 SELECT
子句生成了)。
# 造数据
WITH t AS
(SELECT
1 AS id,
'aaa1' AS tag1,
'bbb1' AS tag2,
NULL AS tag3
UNION
SELECT
2 AS id,
NULL AS tag1,
'bbb2' AS tag2,
'ccc1' AS tag3
UNION
SELECT
3 AS id,
NULL AS tag1,
NULL AS tag2,
'ccc2' AS tag3
UNION
SELECT
4 AS id,
'aaa2' AS tag1,
'bbb3' AS tag2,
'ccc3' AS tag3
UNION
SELECT
5 AS id,
'aaa3' AS tag1,
NULL AS tag2,
NULL AS tag3
UNION
SELECT
6 AS id,
NULL AS tag1,
'bbb4' AS tag2,
'ccc4' AS tag3
UNION
SELECT
7 AS id,
NULL AS tag1,
NULL AS tag2,
'ccc5' AS tag3),
# 去 NULL 值,列转行
t2 AS
(SELECT
tag1 AS tag,
'tag1' AS col,
row_number () over (
ORDER BY id) AS id
FROM
t
WHERE tag1 IS NOT NULL
UNION ALL
SELECT
tag2,
'tag2' AS col,
row_number () over (
ORDER BY id) AS id
FROM
t
WHERE tag2 IS NOT NULL
UNION ALL
SELECT
tag3,
'tag3' AS col,
row_number () over (
ORDER BY id) AS id
FROM
t
WHERE tag3 IS NOT NULL)
# 行转列
SELECT
id,
MAX(IF(col = 'tag1', tag, NULL)) AS tag1,
MAX(IF(col = 'tag2', tag, NULL)) AS tag2,
MAX(IF(col = 'tag3', tag, NULL)) AS tag3
FROM
t2
GROUP BY id
ORDER BY 1
做列转行操作时,按值在原表的列出现的顺序设置了序号,目的是维持同一列中的值的相对顺序不变。