请求编写SQL查询语句判断机票为单程或往返程

现有flights表，需编写SQL查询语句判断每个机票编号对应航段的行程类型（单程/往返程），判定规则如下：

• 规则1：仅包含单个coupon的机票，所有航段判定为单程；
• 规则2：同一机票编号下，若存在两个航段满足：
  1. 其中一个航段的Origin等于另一个航段的Destination；
  2. 其中一个航段的Destination等于另一个航段的Origin；
  3. 两个航段的coupon均≥1；
     则这两个航段判定为往返程，其余航段仍判定为单程。

附示例数据：

TicketNumber    Coupon  Origin  Destination Value
1000    1   USA JPN One-way
1001    1   JPN USA One-way
1002    1   CAN USA Roundtrip
1002    2   USA CAN Roundtrip
1002    3   CAN BAH One-way
1002    4   BAH USA One-way
1003    1   BRA COL One-way
1003    2   COL MEX Roundtrip
1003    3   MEX COL Roundtrip
1004    1   KOR IND One-way
1005    1   KOR JPN One-way
1005    2   JPN USA One-way
1005    3   USA KOR One-way
1006    1   IND GBR Roundtrip
1006    2   GBR IND Roundtrip
1007    1   CHN JPN One-way
1008    1   SWE DEN Roundtrip
1008    2   DEN SWE Roundtrip
1009    1   MEX USA One-way
1009    2   USA CAN One-way
1010    1   FRA GBR One-way

通过自连接结合条件判断即可实现需求，核心是为每个航段匹配同机票下满足往返规则的对应航段，再结合单coupon规则完成最终判定。

SQL查询语句 #

SELECT 
    f1.TicketNumber,
    f1.Coupon,
    f1.Origin,
    f1.Destination,
    CASE
        -- 规则1：单coupon机票直接判定为单程
        WHEN (SELECT COUNT(*) FROM flights f WHERE f.TicketNumber = f1.TicketNumber) = 1 THEN 'One-way'
        -- 规则2：存在匹配的往返航段则判定为往返程
        WHEN EXISTS (
            SELECT 1 
            FROM flights f2 
            WHERE f2.TicketNumber = f1.TicketNumber
              AND f2.Coupon >= 1
              AND f1.Coupon >= 1
              AND f2.Origin = f1.Destination
              AND f2.Destination = f1.Origin
              -- 避免重复匹配同一对往返航段
              AND f2.Coupon > f1.Coupon
        ) THEN 'Roundtrip'
        -- 其余情况判定为单程
        ELSE 'One-way'
    END AS TripType
FROM flights f1
ORDER BY f1.TicketNumber, f1.Coupon;

逻辑说明 #

1. 单coupon判定：通过子查询统计当前机票的总coupon数量，若为1则直接返回One-way；
2. 往返程匹配：用EXISTS子查询在同机票编号下，查找满足起终点互逆、coupon≥1且coupon编号更大的航段（避免同一对往返航段被重复标记），存在则返回Roundtrip；
3. 默认情况：不满足上述条件的航段统一返回One-way。

执行该语句后，输出结果会与示例中的Value列完全匹配。

内容的提问来源于stack exchange，提问作者Sherlan Emmanuel Burgonia