删除列中的 NULL 值
今天接到一个群友的需求，有一张表的数据如图 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 aaa3t2 表的数值如下：
id tag
------ --------
1 bbb1
2 bbb2
3 bbb3
4 bbb4t3 表的数值如下：
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 做列转行操作时，按值在原表的列出现的顺序设置了序号，目的是维持同一列中的值的相对顺序不变。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-05-16，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读