如何使用 case when 获得两列的正确总和

Question

我正在开发一个程序，该程序将跟踪销售人员销售的单位，这些单位可以是完整交易 (1) 或半交易 (0.5)。我需要做的是找到一种方法来获取按销售人员 ID 分组的全部交易和一半交易的总和。

这是数据库结构：

id	salesperson_id	salesperson_two_id	已售出日期
1	5	空	2022-07-02
2	3	5	2022-07-18
3	4	空	2022-07-16
4	5	3	2022-07-12
5	3	5	2022-07-17
6	5	空	2022-07-18

如果我只想检索一位销售人员的 SUM，我有一个有效的查询：

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;

输出将如预期：

全部交易	一半交易
2	1.5

我想要完成的是为表中的所有销售人员获取这些结果，但不知道如何实现它。这是我想要得到的结果：

salesperson_id	总交易
5	3.5
3	1.5
4	1

如果可能的话，我希望结果按总交易数排序。

Answer 1

使用 UNION ALL 获取包含每个销售人员的所有行的结果集，筛选出您想要的月份并聚合：

SELECT salesperson_id,
       SUM(CASE WHEN salesperson_two_id IS NULL THEN 1 ELSE 0.5 END) totalDeals
FROM (
  SELECT salesperson_id, salesperson_two_id, sold_date FROM sold_logs
  UNION ALL
  SELECT salesperson_two_id, salesperson_id, sold_date FROM sold_logs WHERE salesperson_two_id IS NOT NULL
) t
WHERE MONTH(sold_date) = 7 AND YEAR(sold_date) = 2022
GROUP BY salesperson_id
ORDER BY totalDeals DESC;

查看演示。