如何改写无ON后附加条件且结果一致的LEFT JOIN查询？及尝试方案失效原因咨询

嘿，我来帮你拆解这个问题，先搞懂原查询的逻辑，再看你两次尝试哪里错了，最后给你正确的改写方式。

首先，先明确你原查询的核心逻辑：
你写的原查询是：

SELECT * 
FROM TEMP_AVIA_EVENT AE 
LEFT JOIN TEMP_AVIA_EVENT A 
  ON AE.pnr_id = A.pnr_id 
  AND AE.operation_type_id IN (4, 6) 
  AND A.operation_type_id IN (17, 9);

这个查询的行为是：

• 保留所有TEMP_AVIA_EVENT表的行作为左表数据（不管operation_type_id是什么）
• 只有当左表行的operation_type_id在(4,6)时，才会去右表找pnr_id相同且operation_type_id在(17,9)的行；如果左表行的operation_type_id不在(4,6)，哪怕有匹配的pnr_id，右表部分也会返回NULL

现在来看你两次尝试为什么失效：

第一次尝试的问题 #

你写的CTE是：

WITH AE AS ( SELECT * FROM TEMP_AVIA_EVENT WHERE operation_type_id IN (4, 6) ), 
A AS ( SELECT * FROM TEMP_AVIA_EVENT WHERE operation_type_id IN (17, 9) ), 
SELECT * FROM AE LEFT JOIN A ON AE.pnr_id = A.pnr_id;

这里你直接把左表AE过滤成了只有operation_type_id在(4,6)的行，但原查询是保留所有左表行的——那些operation_type_id不在(4,6)的行，原查询里是会留在结果里（只是右表为NULL），但你这个尝试直接把这些行删掉了，所以返回的行数自然比原查询少很多。

第二次尝试的问题 #

你的第二个CTE：

WITH AE AS ( SELECT * FROM TEMP_AVIA_EVENT), 
A AS ( SELECT * FROM TEMP_AVIA_EVENT WHERE operation_type_id IN (17, 9) AND operation_type_id IN (4, 6)), 
SELECT * FROM AE LEFT JOIN A ON AE.pnr_id = A.pnr_id;

这里的致命错误是A表的过滤条件：operation_type_id IN (17,9) AND operation_type_id IN (4,6)。一个数值不可能同时属于两个完全不重叠的集合（17、9和4、6没有任何相同的数），所以这个A表实际上是空的。这就导致你LEFT JOIN之后，所有行的右表部分都是NULL，但原查询里那些左表行满足operation_type_id IN (4,6)且有匹配pnr_id的行，是会返回右表有效数据的，所以你的结果自然和原查询不符。

正确的改写方式（ON后仅保留pnr_id相等） #

如果你的需求是ON后面只能有AE.pnr_id = A.pnr_id这一个条件，同时要和原查询结果完全一致，你可以用以下方式：

WITH filtered_right AS (
    -- 先把右表需要的数据过滤好
    SELECT * FROM TEMP_AVIA_EVENT WHERE operation_type_id IN (17, 9)
)
SELECT
    AE.*,
    -- 只有左表行满足operation_type_id在(4,6)时，才取右表数据，否则返回NULL
    CASE WHEN AE.operation_type_id IN (4, 6) THEN filtered_right.pnr_id ELSE NULL END AS a_pnr_id,
    CASE WHEN AE.operation_type_id IN (4, 6) THEN filtered_right.operation_type_id ELSE NULL END AS a_operation_type_id,
    -- 其他右表字段都用同样的CASE逻辑包裹，和上面保持一致
FROM TEMP_AVIA_EVENT AE
LEFT JOIN filtered_right ON AE.pnr_id = filtered_right.pnr_id;

这个写法的核心是：先过滤好右表的数据，然后在SELECT阶段，通过CASE语句控制只有当左表行满足operation_type_id IN (4,6)时，才返回右表的有效数据，否则返回NULL，完美复刻原查询的逻辑。

内容来源于stack exchange