JOIN后如何按条件GROUP BY并聚合字段的SQL实现

问题说明 #

现有通过以下查询生成的数据集：

原查询语句：

WITH ws AS (select date, quantity,
name, season_id, contract_id, contract.signing_date
FROM warehouse_state
JOIN inventory ON inventory.id = warehouse_state.inventory_id
JOIN owner ON owner.inventory_id = warehouse_state.id
JOIN season ON season.id = owner.season_id
JOIN contract ON contract.id = warehouse_contract.contract_id
GROUP BY date, quantity, name, season.id, contract.id, signing_date)

需求：按contract_id分组，计算**所有仓库状态记录中date早于对应合同signing_date**的quantity总和，无符合条件记录时返回0，预期输出：

聚合规则：

• contract_id=3000：记录1、3、4的date早于2016-10-20，总和3+10+5=18
• contract_id=3541：仅记录1、3、4的date早于2021-01-28，总和3+10+5=18
• contract_id=1528：无记录的date早于2016-03-02，总和为0

解决方案 #

方案一：使用WITH子句（结构清晰，推荐） #

WITH ws AS (
    SELECT 
        date, 
        quantity,
        name, 
        c.contract_id, 
        c.signing_date
    FROM warehouse_state ws
    JOIN inventory i ON i.id = ws.inventory_id
    JOIN owner o ON o.inventory_id = ws.id
    JOIN season s ON s.id = o.season_id
    -- 修正关联逻辑：先关联中间表warehouse_contract，再关联contract
    JOIN warehouse_contract wc ON wc.warehouse_state_id = ws.id
    JOIN contract c ON c.id = wc.contract_id
    GROUP BY date, quantity, name, c.contract_id, c.signing_date
),
unique_contracts AS (
    -- 提取唯一合同信息，避免重复计算
    SELECT DISTINCT 
        contract_id, 
        signing_date, 
        name
    FROM ws
)
SELECT 
    uc.contract_id,
    uc.signing_date,
    -- 用COALESCE将NULL转为0，确保无符合条件记录时返回0
    COALESCE(SUM(ws.quantity), 0) AS quantity,
    uc.name
FROM unique_contracts uc
-- LEFT JOIN确保所有合同都被保留，即使没有符合条件的记录
LEFT JOIN ws ON ws.date < uc.signing_date
GROUP BY uc.contract_id, uc.signing_date, uc.name
ORDER BY uc.contract_id;

方案二：不使用WITH子句（嵌套子查询实现） #

SELECT 
    uc.contract_id,
    uc.signing_date,
    COALESCE(SUM(ws.quantity), 0) AS quantity,
    uc.name
FROM (
    -- 提取唯一合同信息
    SELECT DISTINCT 
        c.contract_id, 
        c.signing_date, 
        name
    FROM warehouse_state ws
    JOIN inventory i ON i.id = ws.inventory_id
    JOIN owner o ON o.inventory_id = ws.id
    JOIN season s ON s.id = o.season_id
    JOIN warehouse_contract wc ON wc.warehouse_state_id = ws.id
    JOIN contract c ON c.id = wc.contract_id
) uc
LEFT JOIN (
    -- 获取所有仓库状态记录
    SELECT 
        date, 
        quantity,
        name
    FROM warehouse_state ws
    JOIN inventory i ON i.id = ws.inventory_id
    JOIN owner o ON o.inventory_id = ws.id
    JOIN season s ON s.id = o.season_id
    JOIN warehouse_contract wc ON wc.warehouse_state_id = ws.id
    JOIN contract c ON c.id = wc.contract_id
) ws ON ws.date < uc.signing_date
GROUP BY uc.contract_id, uc.signing_date, uc.name
ORDER BY uc.contract_id;

关键说明 #

1. 关联逻辑修正：原查询未关联warehouse_state与warehouse_contract，可能导致笛卡尔积，方案中补充了该关联条件，需根据实际表结构调整字段名（如wc.warehouse_state_id）。
2. 唯一合同提取：用DISTINCT提取唯一合同，避免同一合同被多次计算。
3. LEFT JOIN + COALESCE：确保所有合同都出现在结果中，无符合条件记录时总和返回0而非NULL。

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