如何用CASE WHEN按销售ID分组统计完整/半单总和并排序

需求说明 #

开发销售业绩追踪程序，统计销售人员成交单量：

• 仅单个销售人员参与的订单为完整单，计1
• 两位销售人员共同参与的订单为半单，每人计0.5
  需按salesperson_id分组，统计每位销售人员的总单量（totalDeals），并按totalDeals降序排序。

现有SQL（单销售ID查询） #

SELECT 
SUM(case when salesperson_id = 5 and isnull(salesperson_two_id) then 1 end) as fullDeals, 
SUM(case when salesperson_id != 5 and salesperson_two_id = 5 
or salesperson_id = 5 and salesperson_two_id != 5 then 0.5 end) as halfDeals 
FROM sold_logs WHERE MONTH(sold_date) = 07 AND YEAR(sold_date) = 2022;

数据库结构 #

期望结果 #

解决方案SQL #

方法一：使用CTE拆分订单贡献（推荐，逻辑清晰） #

WITH sales_contributions AS (
    -- 主销售人员的订单贡献
    SELECT 
        salesperson_id AS sp_id,
        CASE WHEN salesperson_two_id IS NULL THEN 1 ELSE 0.5 END AS deal_value
    FROM sold_logs
    WHERE MONTH(sold_date) = 7 AND YEAR(sold_date) = 2022
    UNION ALL
    -- 共同销售人员的订单贡献（仅当存在时）
    SELECT 
        salesperson_two_id AS sp_id,
        0.5 AS deal_value
    FROM sold_logs
    WHERE salesperson_two_id IS NOT NULL 
      AND MONTH(sold_date) = 7 AND YEAR(sold_date) = 2022
)
SELECT 
    sp_id AS salesperson_id,
    SUM(deal_value) AS totalDeals
FROM sales_contributions
GROUP BY sp_id
ORDER BY totalDeals DESC;

方法二：通过子查询关联所有销售ID #

SELECT 
    sp.salesperson_id,
    SUM(
        CASE 
            -- 唯一参与人得1
            WHEN sl.salesperson_id = sp.salesperson_id AND sl.salesperson_two_id IS NULL THEN 1
            -- 共同参与人得0.5
            WHEN (sl.salesperson_id = sp.salesperson_id AND sl.salesperson_two_id IS NOT NULL) 
                 OR sl.salesperson_two_id = sp.salesperson_id THEN 0.5
            ELSE 0
        END
    ) AS totalDeals
FROM (
    -- 获取所有存在的销售人员ID
    SELECT salesperson_id FROM sold_logs
    UNION
    SELECT salesperson_two_id FROM sold_logs WHERE salesperson_two_id IS NOT NULL
) sp
LEFT JOIN sold_logs sl 
    ON sp.salesperson_id = sl.salesperson_id 
    OR sp.salesperson_id = sl.salesperson_two_id
WHERE MONTH(sl.sold_date) = 7 AND YEAR(sl.sold_date) = 2022
GROUP BY sp.salesperson_id
ORDER BY totalDeals DESC;

逻辑说明 #

1. 两种方法核心都是拆分每条订单中销售人员的贡献值：
   • 无共同销售的订单，唯一销售得1
   • 有共同销售的订单，两位销售各得0.5
2. 最终通过分组求和得到每位销售的总单量，并按总单量降序排序。

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