SQLite(QGIS环境)如何用UNION SELECT同时返回多表行数及合计值

解决方案 #

你可以直接在原有各表统计的UNION语句后，追加一行汇总查询的结果即可，符合SQLite语法规范的写法如下：

SELECT 'S_MH_DSC_Abandoned' Table_N, COUNT(*) Count FROM S_MH_DSC_Abandoned
UNION
SELECT 'S_MH_DSC_ExclAbandoned' Table_N, COUNT(*) Count FROM S_MH_DSC_ExclAbandoned
UNION
SELECT 'S_MH_Private_All' Table_N, COUNT(*) Count FROM S_MH_Private_All
UNION
SELECT 'Sum Total' Table_N, SUM(Count) Count
FROM (
    SELECT COUNT(*) Count FROM S_MH_DSC_Abandoned
    UNION ALL
    SELECT COUNT(*) Count FROM S_MH_DSC_ExclAbandoned
    UNION ALL
    SELECT COUNT(*) Count FROM S_MH_Private_All
) AS temp

注意事项

• 汇总部分的子查询要用UNION ALL，避免两个表行数相同时被UNION去重，导致求和结果错误
• 如果需要确保汇总行固定在结果末尾，可新增排序字段控制顺序，写法如下：

SELECT Table_N, Count
FROM (
    SELECT 1 sort_order, 'S_MH_DSC_Abandoned' Table_N, COUNT(*) Count FROM S_MH_DSC_Abandoned
    UNION
    SELECT 1 sort_order, 'S_MH_DSC_ExclAbandoned' Table_N, COUNT(*) Count FROM S_MH_DSC_ExclAbandoned
    UNION
    SELECT 1 sort_order, 'S_MH_Private_All' Table_N, COUNT(*) Count FROM S_MH_Private_All
    UNION
    SELECT 2 sort_order, 'Sum Total' Table_N, SUM(Count) Count
    FROM (
        SELECT COUNT(*) Count FROM S_MH_DSC_Abandoned
        UNION ALL
        SELECT COUNT(*) Count FROM S_MH_DSC_ExclAbandoned
        UNION ALL
        SELECT COUNT(*) Count FROM S_MH_Private_All
    ) AS temp
) AS all_result
ORDER BY sort_order, Table_N

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