如何合并两个分组一致的SQL查询，实现结果同栏展示？

我完全理解你的困扰——两个单独的查询都能给出正确结果，但合并时却因为表关联导致统计数值出错，核心原因是Payments和Orders之间没有直接关联关系，直接关联三张表会产生笛卡尔积，让SUM/COUNT重复计算两边的记录，最终得到错误的结果。

下面提供两种可行的解决方案，都是先分别完成两个查询的聚合统计，再通过相同的分组字段关联，彻底避免笛卡尔积问题：

方案1：子查询关联 #

先将两个查询各自作为子查询完成分组统计，再通过Z_Number和Z_Date关联结果，确保每一行都是单个WorkPeriod的完整统计：

SELECT 
    q1.Z_Number,
    q1.Z_Date,
    q1.Cash_Payments,
    q1.No_of_Tickets_Cash,
    q1.Credit_Card_Payments,
    q1.No_of_Tickets_Credit_Card,
    q2.Gifts_Amount,
    q2.No_of_Gift_Orders
FROM (
    -- 支付统计子查询
    SELECT 
        WorkPeriods.Id AS Z_Number,
        CONVERT(VARCHAR, (CONVERT(DATE, WorkPeriods.StartDate, 103)), 103) AS Z_Date,
        SUM(CASE WHEN Payments.Name = 'Cash' THEN Payments.Amount ELSE 0 END) AS Cash_Payments,
        COUNT(CASE WHEN Payments.Name = 'Cash' THEN 1 END) AS No_of_Tickets_Cash,
        SUM(CASE WHEN Payments.Name = 'Credit Card' THEN Payments.Amount ELSE 0 END) AS Credit_Card_Payments,
        COUNT(CASE WHEN Payments.Name = 'Credit Card' THEN 1 END) AS No_of_Tickets_Credit_Card
    FROM Payments
    JOIN WorkPeriods ON Payments.Date BETWEEN WorkPeriods.StartDate AND WorkPeriods.EndDate
    GROUP BY WorkPeriods.Id, WorkPeriods.StartDate
) q1
LEFT JOIN (
    -- 礼品订单统计子查询
    SELECT 
        WorkPeriods.Id AS Z_Number,
        CONVERT(VARCHAR, (CONVERT(DATE, WorkPeriods.StartDate, 103)), 103) AS Z_Date,
        SUM(CASE WHEN Orders.CalculatePrice = 0 THEN Orders.Quantity * Orders.Price ELSE 0 END) AS Gifts_Amount,
        SUM(CASE WHEN Orders.CalculatePrice = 0 THEN Orders.Quantity ELSE 0 END) AS No_of_Gift_Orders
    FROM Orders
    JOIN WorkPeriods ON Orders.CreatedDateTime BETWEEN WorkPeriods.StartDate AND WorkPeriods.EndDate
    GROUP BY WorkPeriods.Id, WorkPeriods.StartDate
) q2 ON q1.Z_Number = q2.Z_Number AND q1.Z_Date = q2.Z_Date

方案2：CTE（公共表表达式）关联 #

和子查询逻辑一致，但用CTE拆分统计模块，代码可读性更强，后期维护更方便：

WITH PaymentStats AS (
    -- 定义支付统计CTE
    SELECT 
        WorkPeriods.Id AS Z_Number,
        CONVERT(VARCHAR, (CONVERT(DATE, WorkPeriods.StartDate, 103)), 103) AS Z_Date,
        SUM(CASE WHEN Payments.Name = 'Cash' THEN Payments.Amount ELSE 0 END) AS Cash_Payments,
        COUNT(CASE WHEN Payments.Name = 'Cash' THEN 1 END) AS No_of_Tickets_Cash,
        SUM(CASE WHEN Payments.Name = 'Credit Card' THEN Payments.Amount ELSE 0 END) AS Credit_Card_Payments,
        COUNT(CASE WHEN Payments.Name = 'Credit Card' THEN 1 END) AS No_of_Tickets_Credit_Card
    FROM Payments
    JOIN WorkPeriods ON Payments.Date BETWEEN WorkPeriods.StartDate AND WorkPeriods.EndDate
    GROUP BY WorkPeriods.Id, WorkPeriods.StartDate
),
OrderStats AS (
    -- 定义礼品订单统计CTE
    SELECT 
        WorkPeriods.Id AS Z_Number,
        CONVERT(VARCHAR, (CONVERT(DATE, WorkPeriods.StartDate, 103)), 103) AS Z_Date,
        SUM(CASE WHEN Orders.CalculatePrice = 0 THEN Orders.Quantity * Orders.Price ELSE 0 END) AS Gifts_Amount,
        SUM(CASE WHEN Orders.CalculatePrice = 0 THEN Orders.Quantity ELSE 0 END) AS No_of_Gift_Orders
    FROM Orders
    JOIN WorkPeriods ON Orders.CreatedDateTime BETWEEN WorkPeriods.StartDate AND WorkPeriods.EndDate
    GROUP BY WorkPeriods.Id, WorkPeriods.StartDate
)
-- 关联两个CTE得到最终结果
SELECT 
    ps.Z_Number,
    ps.Z_Date,
    ps.Cash_Payments,
    ps.No_of_Tickets_Cash,
    ps.Credit_Card_Payments,
    ps.No_of_Tickets_Credit_Card,
    os.Gifts_Amount,
    os.No_of_Gift_Orders
FROM PaymentStats ps
LEFT JOIN OrderStats os ON ps.Z_Number = os.Z_Number AND ps.Z_Date = os.Z_Date

额外提示 #

• 我把你原来的逗号分隔表的写法改成了显式JOIN，这是现代SQL的标准写法，能更直观地表达表之间的关联逻辑，避免意外的笛卡尔积。
• 用LEFT JOIN是为了确保即使某个WorkPeriod没有礼品订单，也能正常显示支付统计结果；如果只需要显示两边都有数据的记录，可以改成INNER JOIN。

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