如何在SQLite中无需JSON方法提取三列非空唯一值至新列

需求说明 #

提取三列数据中非NULL且非空字符串的唯一值，以逗号分隔形式生成新列，要求不使用JSON相关函数，同时避免拼接时出现多余空项。

解决方案 #

通过将列转换为行记录，过滤无效值后去重拼接，是SQLite中实现该需求的简洁方案，可直接在SQLite Fiddle运行：

1. 创建测试表与数据

CREATE TABLE test_data (
    id INTEGER PRIMARY KEY,
    col1 TEXT,
    col2 TEXT,
    col3 TEXT
);

INSERT INTO test_data VALUES
(1, 'a', 'b', 'a'),
(2, '', NULL, 'c'),
(3, 'd', '', 'd'),
(4, NULL, NULL, NULL),
(5, 'e', 'f', 'g');

2. 核心查询语句

SELECT
    t.id,
    IFNULL(GROUP_CONCAT(DISTINCT filtered.val, ','), '') AS unique_nonempty_vals
FROM test_data t
LEFT JOIN (
    -- 提取每一列的有效值（非NULL、非空字符串）
    SELECT id, col1 AS val FROM test_data WHERE col1 IS NOT NULL AND col1 != ''
    UNION ALL
    SELECT id, col2 AS val FROM test_data WHERE col2 IS NOT NULL AND col2 != ''
    UNION ALL
    SELECT id, col3 AS val FROM test_data WHERE col3 IS NOT NULL AND col3 != ''
) filtered ON t.id = filtered.id
GROUP BY t.id;

逻辑说明 #

• 列转行：通过UNION ALL将三列的有效值分别拆分为行记录，关联原表的id确保数据归属正确
• 过滤无效值：在子查询中通过WHERE colX IS NOT NULL AND colX != ''排除空值与空字符串
• 去重拼接：使用GROUP_CONCAT(DISTINCT ...)对同一id下的有效值去重后，以逗号分隔拼接
• 空结果处理：IFNULL函数将无有效值的情况转为空字符串（若保留NULL可去掉此函数）

查询结果 #

优势对比 #

相比嵌套大量CASE逻辑判断等值、空值的方案，此方法代码更简洁易维护，同时避免了CONCAT_WS无法过滤空字符串导致的多余空项问题（CONCAT_WS仅忽略NULL，不会排除空字符串）。

内容的提问来源于stack exchange，提问作者Gary