能否使用子查询合并按国家、美国按州聚合的两个SQL查询？

实现方案 #

完全可以通过单个查询实现需求，也支持通过子查询实现，不过更推荐以下两种兼容性、性能更优的实现方式：

方案1：UNION ALL 合并结果集（全数据库兼容） #

直接合并两个查询的结果，通过CTE封装公共过滤逻辑避免重复代码，所有SQL数据库都支持该写法：

WITH valid_invoices AS (
    SELECT
        customer.country,
        customer.state,
        i.subtotal
    FROM invoices i
    LEFT JOIN customer ON i.customer_id = customer.id
    WHERE
        status = 'Paid'
        AND datepaid BETWEEN '2020-05-01 00:00:00' AND '2020-06-01 00:00:00'
        AND customer.billing_day <> 0
        AND customer.register_date < '2020-06-01 00:00:00'
        AND customer.account_exempt = 'f'
        AND customer.country <> ''
)
-- 国家维度聚合结果
SELECT
    '国家' AS dimension_type,
    country AS dimension_value,
    SUM(subtotal) AS total
FROM valid_invoices
GROUP BY country
UNION ALL
-- 美国州维度聚合结果
SELECT
    '美国-州' AS dimension_type,
    state AS dimension_value,
    SUM(subtotal) AS total
FROM valid_invoices
WHERE country = 'US' AND state <> ''
GROUP BY state
ORDER BY dimension_type, dimension_value;

方案2：GROUPING SETS 一次聚合（高性能，适配现代SQL数据库） #

如果你的数据库支持GROUPING SETS（MySQL 8.0+、PostgreSQL、SQL Server、Oracle均支持），可以只扫描一次数据直接生成两种维度的聚合结果，性能比UNION ALL更高：

SELECT
    CASE
        WHEN GROUPING(customer.state) = 1 THEN '国家'
        ELSE '美国-州'
    END AS dimension_type,
    CASE
        WHEN GROUPING(customer.state) = 1 THEN customer.country
        ELSE customer.state
    END AS dimension_value,
    SUM(i.subtotal) AS total
FROM invoices i
LEFT JOIN customer ON i.customer_id = customer.id
WHERE
    status = 'Paid'
    AND datepaid BETWEEN '2020-05-01 00:00:00' AND '2020-06-01 00:00:00'
    AND customer.billing_day <> 0
    AND customer.register_date < '2020-06-01 00:00:00'
    AND customer.account_exempt = 'f'
    AND customer.country <> ''
    AND (customer.country <> 'US' OR (customer.country = 'US' AND customer.state <> ''))
GROUP BY GROUPING SETS (
    (customer.country), -- 按国家分组
    (customer.country, customer.state) -- 按美国+州分组
)
HAVING GROUPING(customer.country) = 0 -- 排除全局总合计行
ORDER BY dimension_type, dimension_value;

补充说明 #

你提供的第一个原始SQL存在语法错误：customer.country <> '' 前缺少AND关键字，使用时请注意修正。

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