SQL Server按BranchId统计两类有效合同数量的问题求助

我的问题和类似的SQL统计场景思路一致，但没法调整出适配自身需求的方案。

表结构 #

同事合同表hr.Contract结构如下：

ContractId int PK 
ColleagueId int [not null]
ContractStart datetime2 [not null]
ContractEnd datetime2 [null]
BranchId int [not null]
SaturdayOnly bit
isActive bit

统计需求 #

按BranchId分别统计两类有效合同的数量：

• 仅周六生效（SaturdayOnly=1）的有效合同数
• 非仅周六生效（SaturdayOnly=0）的有效合同数

规则说明：

• 一位同事在同一分支可有多份合同，但仅一份为有效状态（isActive=1）
• 合同需满足：开始时间早于2022-12-01，若存在结束时间则需晚于2022-12-01

尝试的SQL及问题 #

我写了以下SQL，但两个统计结果完全相同，分支的计数也明显不对：

WITH cte AS 
(
    SELECT 
        co.BranchId, co.ContractId, co.ColleagueId,
        ROW_NUMBER() OVER (PARTITION BY co.ColleagueId ORDER BY co.ContractStart DESC) AS row_number
    FROM 
        hr.Contract co    
    WHERE 
        co.SaturdayOnly = 0 
        AND (co.ContractEnd IS NULL OR co.ContractEnd > '2022-12-01')
),
cte_sat AS 
(
    SELECT 
        co.BranchId, co.ContractId, co.ColleagueId,
        ROW_NUMBER() OVER (PARTITION BY co.ColleagueId ORDER BY co.ContractStart DESC) AS row_number
    FROM 
        hr.Contract co    
    WHERE 
        co.SaturdayOnly = 1 
        AND (co.ContractEnd IS NULL OR co.ContractEnd > '2022-12-01')
)
SELECT 
    b.BranchName, 
    COUNT(cte.ContractId), COUNT(cte_sat.ContractId)
FROM 
    hr.Branch b
JOIN 
    cte ON b.ContractorCode = cte.BranchId
JOIN 
    cte_sat ON b.ContractorCode = cte_sat.BranchId
WHERE 
    cte.row_number = 1
GROUP BY 
    b.BranchNumber, b.BranchName
ORDER BY 
    b.BranchNumber

原SQL的问题分析 #

1. 双CTE关联导致重复计数：直接把两个CTE用JOIN关联，会让非周六合同和仅周六合同形成交叉匹配，每一行非周六合同都会和同分支的所有仅周六合同配对，导致计数被重复计算。
2. 遗漏有效合同条件：需求明确只统计isActive=1的合同，但你的CTE完全没加这个过滤条件，会把无效合同也纳入统计。
3. 分区逻辑错误：需求是同一同事在同一分支仅一份有效合同，所以分区应该是PARTITION BY ColleagueId, BranchId，而不是只按ColleagueId分区，否则会把同事在其他分支的合同也混进来。

正确的SQL写法 #

先筛选出符合所有条件的有效合同，再按分支和合同类型分别统计：

WITH ValidContracts AS (
    SELECT 
        BranchId,
        SaturdayOnly,
        -- 按“同事+分支”分区，取每个组合下最新的有效合同
        ROW_NUMBER() OVER (PARTITION BY ColleagueId, BranchId ORDER BY ContractStart DESC) AS rn
    FROM hr.Contract
    WHERE 
        isActive = 1 -- 仅统计有效合同
        AND ContractStart < '2022-12-01' -- 开始时间早于指定日期
        AND (ContractEnd IS NULL OR ContractEnd > '2022-12-01') -- 结束时间符合要求
)
SELECT 
    b.BranchName,
    -- 统计非仅周六的有效合同数量
    SUM(CASE WHEN vc.SaturdayOnly = 0 THEN 1 ELSE 0 END) AS NonSaturdayCount,
    -- 统计仅周六的有效合同数量
    SUM(CASE WHEN vc.SaturdayOnly = 1 THEN 1 ELSE 0 END) AS SaturdayOnlyCount
FROM hr.Branch b
LEFT JOIN ValidContracts vc 
    ON b.ContractorCode = vc.BranchId 
    AND vc.rn = 1 -- 只取每个同事+分支下的有效合同
GROUP BY b.BranchNumber, b.BranchName
ORDER BY b.BranchNumber;

说明 #

• 单个CTE完成有效合同的筛选和去重，避免多CTE关联的问题。
• 用CASE WHEN在同一查询中分别统计两类合同的数量，逻辑清晰且高效。
• 使用LEFT JOIN确保即使分支没有对应合同，也会显示该分支（计数为0），如果不需要显示无合同的分支，可改成INNER JOIN。

内容的提问来源于stack exchange，提问作者Colin-G-Davidson