SQLite中使用Group By填充1-10缺失DataID对应null值的方法

原始数据与问题 #

现有DataTest表结构及数据如下：

执行查询SELECT theData FROM DataTest GROUP BY dataID;后，结果缺少DataID为3、6、7的行，需要补全这些行并对应填充null。

解决方案 #

核心思路是先构造1到10的完整数字序列，再通过左连接关联原表分组后的结果，确保每个DataID都被保留，缺失的自动填充null。

方法1：递归CTE生成序列（推荐，适合范围较大的场景）

WITH RECURSIVE num_seq AS (
    SELECT 1 AS DataID
    UNION ALL
    SELECT DataID + 1 FROM num_seq WHERE DataID < 10
)
SELECT dt.theData
FROM num_seq ns
LEFT JOIN (
    SELECT DataID, theData 
    FROM DataTest 
    GROUP BY DataID
) dt ON ns.DataID = dt.DataID
ORDER BY ns.DataID;

方法2：手动构造序列（适合固定小范围）

如果范围固定且很小，可直接用UNION ALL手动生成序列：

SELECT dt.theData
FROM (
    SELECT 1 AS DataID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) ns
LEFT JOIN (
    SELECT DataID, theData 
    FROM DataTest 
    GROUP BY DataID
) dt ON ns.DataID = dt.DataID
ORDER BY ns.DataID;

注意事项 #

原查询中GROUP BY DataID但直接选择theData属于SQLite的非标准行为（theData既不在分组字段中，也未使用聚合函数），实际取值不确定。如果需要明确取某类值（比如每组的最大值、最小值），建议改用聚合函数，示例如下：

WITH RECURSIVE num_seq AS (
    SELECT 1 AS DataID
    UNION ALL
    SELECT DataID + 1 FROM num_seq WHERE DataID < 10
)
SELECT MAX(dt.theData) AS theData
FROM num_seq ns
LEFT JOIN DataTest dt ON ns.DataID = dt.DataID
GROUP BY ns.DataID
ORDER BY ns.DataID;

最终结果 #

执行上述查询后，将得到包含1-10所有DataID对应行的结果，缺失的3、6、7行对应theData为null，与目标结果一致。

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