如何将多个按日期分组的SQL统计查询合并为单个查询

嘿，我来帮你把那些分散的SQL查询合并成一个，再也不用导出Excel折腾VLOOKUP啦！

核心思路很简单：因为你的每个查询都是按相同的分组字段（比如你示例里的LocationStateAbr+日期）统计的，我们可以把每个统计结果先单独拎出来（用CTE或者子查询），然后通过分组字段把它们关联起来，一次性拿到所有统计数据。

先补全你的示例场景 #

假设你的两个查询是这样的（我补全了日期分组和表名，你可以根据实际情况调整）：

• 查询1：统计各地区每日的代理总数

SELECT 
    LocationStateAbr,
    DATE_TRUNC('day', RecordDate) AS GroupedDate, -- 假设日期字段是RecordDate，按天分组
    COUNT(LocationStateAbr) AS "Total Agencies"
FROM [PROD_Agency].[dbo].[AgencyRecords]
GROUP BY LocationStateAbr, DATE_TRUNC('day', RecordDate)

• 查询2：统计各地区每日的活跃代理数（举例）

SELECT 
    LocationStateAbr,
    DATE_TRUNC('day', RecordDate) AS GroupedDate,
    COUNT(DISTINCT AgencyID) AS "Active Agencies"
FROM [PROD_Agency].[dbo].[AgencyRecords]
WHERE IsActive = 1
GROUP BY LocationStateAbr, DATE_TRUNC('day', RecordDate)

方法1：用CTE（公共表表达式）+ 全外连接 #

这种方法最灵活，适合不同查询来自不同表的情况：

-- 先定义每个统计的CTE
WITH TotalAgencies AS (
    SELECT 
        LocationStateAbr,
        DATE_TRUNC('day', RecordDate) AS GroupedDate,
        COUNT(LocationStateAbr) AS "Total Agencies"
    FROM [PROD_Agency].[dbo].[AgencyRecords]
    GROUP BY LocationStateAbr, DATE_TRUNC('day', RecordDate)
),
ActiveAgencies AS (
    SELECT 
        LocationStateAbr,
        DATE_TRUNC('day', RecordDate) AS GroupedDate,
        COUNT(DISTINCT AgencyID) AS "Active Agencies"
    FROM [PROD_Agency].[dbo].[AgencyRecords]
    WHERE IsActive = 1
    GROUP BY LocationStateAbr, DATE_TRUNC('day', RecordDate)
)
-- 把两个CTE通过分组字段关联起来
SELECT 
    COALESCE(ta.LocationStateAbr, aa.LocationStateAbr) AS LocationStateAbr,
    COALESCE(ta.GroupedDate, aa.GroupedDate) AS GroupedDate,
    ta."Total Agencies",
    aa."Active Agencies"
FROM TotalAgencies ta
FULL OUTER JOIN ActiveAgencies aa
    ON ta.LocationStateAbr = aa.LocationStateAbr
    AND ta.GroupedDate = aa.GroupedDate
ORDER BY GroupedDate, LocationStateAbr;

• 用FULL OUTER JOIN是为了避免某个地区/日期在其中一个查询里没有数据（比如某天某个地区没有活跃代理），导致这条记录丢失
• COALESCE用来处理关联后出现的NULL值，保证分组字段始终有值

方法2：条件聚合（更简洁，适合同一张表的统计） #

如果所有统计都来自同一张表，直接用CASE语句在一个查询里完成所有统计，效率更高：

SELECT 
    LocationStateAbr,
    DATE_TRUNC('day', RecordDate) AS GroupedDate,
    COUNT(LocationStateAbr) AS "Total Agencies",
    -- 只统计IsActive=1的代理ID，NULL不会被COUNT计算
    COUNT(DISTINCT CASE WHEN IsActive = 1 THEN AgencyID END) AS "Active Agencies",
    -- 还可以继续加其他统计，比如新增代理数
    COUNT(CASE WHEN RecordType = 'New' THEN 1 END) AS "New Agencies"
FROM [PROD_Agency].[dbo].[AgencyRecords]
GROUP BY LocationStateAbr, DATE_TRUNC('day', RecordDate)
ORDER BY GroupedDate, LocationStateAbr;

注意事项 #

• 一定要保证所有查询的分组字段完全一致（比如都是LocationStateAbr+按天截断的日期），否则关联后会出现数据不匹配的情况
• 如果你的日期分组不是按天，比如按月/按周，只要把DATE_TRUNC里的参数改成对应的（比如'month'）就行
• 测试的时候可以先单独运行每个CTE或者子查询，确认数据正确后再合并

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