如何在Amazon Redshift中将两个SELECT语句合并为单个输出

嘿，我来帮你把这两个查询结果合并成单个输出！在Redshift里，主要分两种合并场景，取决于你想要的输出格式，我给你分别说清楚：

场景1：把两个统计结果并排展示（同一日期&门店的两个计数放一行） #

这种情况用JOIN就可以，因为你的两个CTE都按日期和门店做了分组，刚好可以用这两个字段作为连接键。这里推荐用FULL OUTER JOIN，避免漏掉其中一个CTE里存在但另一个没有的日期/门店组合，同时可以用COALESCE把NULL值转成0，让结果更整洁：

WITH q1 AS (
    SELECT 
        COUNT(invoices) AS count_q1,
        TO_CHAR(billtime,'yyyy-mm-dd') AS bill_date,
        store
    FROM sales
    GROUP BY TO_CHAR(billtime,'yyyy-mm-dd'), store
), 
q2 AS (
    SELECT 
        COUNT(a.invoices) AS count_q2,
        b.store,
        TO_CHAR(a.billtime,'yyyy-mm-dd') AS bill_date
    FROM sales_detail a
    INNER JOIN dim_store b ON a.store_id = b.id  -- 把隐式连接改成显式INNER JOIN，更清晰
    GROUP BY b.store, TO_CHAR(a.billtime,'yyyy-mm-dd')
)
SELECT 
    COALESCE(q1.bill_date, q2.bill_date) AS bill_date,
    COALESCE(q1.store, q2.store) AS store,
    COALESCE(q1.count_q1, 0) AS sales_invoice_count,
    COALESCE(q2.count_q2, 0) AS sales_detail_invoice_count
FROM q1
FULL OUTER JOIN q2 
    ON q1.bill_date = q2.bill_date 
    AND q1.store = q2.store
ORDER BY bill_date, store;

场景2：把两个统计结果上下堆叠（所有计数行合并到一起） #

如果想把两个查询的结果作为不同的行展示，还可以加上一个标识字段区分来源，用UNION ALL（比UNION高效，因为不会去重）：

WITH q1 AS (
    SELECT 
        COUNT(invoices) AS invoice_count,
        TO_CHAR(billtime,'yyyy-mm-dd') AS bill_date,
        store,
        'sales_table' AS data_source  -- 加个标识字段
    FROM sales
    GROUP BY TO_CHAR(billtime,'yyyy-mm-dd'), store
), 
q2 AS (
    SELECT 
        COUNT(a.invoices) AS invoice_count,
        TO_CHAR(a.billtime,'yyyy-mm-dd') AS bill_date,
        b.store,
        'sales_detail_table' AS data_source  -- 加个标识字段
    FROM sales_detail a
    INNER JOIN dim_store b ON a.store_id = b.id
    GROUP BY b.store, TO_CHAR(a.billtime,'yyyy-mm-dd')
)
SELECT bill_date, store, invoice_count, data_source
FROM q1
UNION ALL
SELECT bill_date, store, invoice_count, data_source
FROM q2
ORDER BY bill_date, store, data_source;

小提示 #

• 我把你原查询里的隐式连接（sales_detail a, dim_store b where a.store_id = b.id）改成了显式INNER JOIN，这是SQL的最佳实践，可读性更强，也不容易出错。
• 如果你的sales表和sales_detail表的store字段定义完全一致（比如都是门店名称），那JOIN/UNION都没问题；如果不一样（比如q1的store是ID，q2是名称），记得先统一字段含义再合并。

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