如何编写SQL查询统计同时含DSV和OWN来源的预订数量

我来帮你搞定这个统计需求！根据你的样例数据和期望结果，我们需要找出所有同时存在DSV和OWN两种Sourcing类型的预订，然后统计这类预订的总数，以及这些预订下的所有项目数量。

方法一：使用CTE（公共表表达式）筛选符合条件的预订 #

这种方法逻辑清晰，先筛选出合格的预订，再关联原表统计总数：

WITH ValidReservations AS (
    SELECT ReservationID
    FROM RESERVATIONS
    GROUP BY ReservationID
    -- 确保该预订同时包含DSV和OWN两种类型
    HAVING MAX(CASE WHEN Sourcing = 'DSV' THEN 1 ELSE 0 END) = 1
       AND MAX(CASE WHEN Sourcing = 'OWN' THEN 1 ELSE 0 END) = 1
       -- 如果你的场景中，预订只能有这两种类型，可加上下面的条件
       -- AND COUNT(DISTINCT Sourcing) = 2
)
SELECT 
    COUNT(DISTINCT vr.ReservationID) AS Reservations,
    COUNT(r.ReservationKey) AS ReservedItems
FROM ValidReservations vr
INNER JOIN RESERVATIONS r 
    ON vr.ReservationID = r.ReservationID;

逻辑解释：

1. CTE ValidReservations：通过分组ReservationID，用CASE语句分别检查每个预订是否存在DSV和OWN的记录。MAX(...) = 1表示该预订至少有一条对应类型的记录。
2. 主查询：将筛选出的合格预订与原表关联，统计唯一的预订数量（Reservations）和所有关联的项目总数（ReservedItems）。

方法二：使用EXISTS子查询 #

如果你更习惯用子查询的写法，也可以这样实现：

SELECT 
    COUNT(DISTINCT r.ReservationID) AS Reservations,
    COUNT(r.ReservationKey) AS ReservedItems
FROM RESERVATIONS r
WHERE 
    -- 检查当前预订存在DSV类型的记录
    EXISTS (
        SELECT 1 
        FROM RESERVATIONS r_dsv 
        WHERE r_dsv.ReservationID = r.ReservationID 
          AND r_dsv.Sourcing = 'DSV'
    )
    -- 同时检查当前预订存在OWN类型的记录
    AND EXISTS (
        SELECT 1 
        FROM RESERVATIONS r_own 
        WHERE r_own.ReservationID = r.ReservationID 
          AND r_own.Sourcing = 'OWN'
    );

逻辑解释：

通过两个EXISTS子查询，直接过滤出同时包含DSV和OWN的预订记录，然后统计唯一预订数和项目总数。

验证结果 #

用你的样例数据测试这两个查询，都会得到：

Reservations | ReservedItems
-------------|--------------
3            | 7

完全匹配你的期望结果！

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