sql - 将 NOT in 转换为 LEFT join 会给出不正确的结果

Question

请帮助我将查询中的 NOT 转换为左连接，因为我想要选择子句中的日期列。我必须将此查询用作 Amazon quicksight 的来源。Quicksight 无法将报告中创建的日期参数传递给我的源查询。所以我必须在 WHERE 子句中获取日期过滤条件。

不在：

SELECT DISTINCT Date(h.Created_Date) DATE, ( h.Vehicle_ID)Decline
FROM awsdatacatalog.waves.Recurring_Transaction_History h  
Left JOIN awsdatacatalog.waves.Wash_Invoice wi  on h.invoice_id=wi.invoice_id

WHERE Date(h.Created_Date) BETWEEN date('2021-05-01') AND date('2021-05-01')

AND h.Status IN ('Declined','DECLINED')
AND h.Vehicle_ID NOT IN (
SELECT Distinct ut.vehicle_Id
FROM awsdatacatalog.waves.Unlimited_Wash_Transaction ut 
WHERE   (Is_Refunded IS NULL OR CAST(Is_Refunded AS INTEGER) =0)
AND (Status ='RECURRING' or Status ='RESIGNUP')
AND DATE(DATE) BETWEEN date('2021-05-01') AND date('2021-05-01')
)

左连接查询：

SELECT DISTINCT Date(h.Created_Date) DATE, ( h.Vehicle_ID)Decline
FROM awsdatacatalog.waves.Recurring_Transaction_History h  
Left JOIN awsdatacatalog.waves.Wash_Invoice wi  on h.invoice_id=wi.invoice_id
LEFT JOIN (
SELECT Distinct ut.vehicle_Id, DATE(DATE) DATE
FROM awsdatacatalog.waves.Unlimited_Wash_Transaction ut 
WHERE   (Is_Refunded IS NULL OR CAST(Is_Refunded AS INTEGER) =0)
AND (Status ='RECURRING' or Status ='RESIGNUP')

) A
ON  H.Vehicle_ID = A.Vehicle_ID
AND  DATE(h.Created_Date) <= A.DATE
WHERE Date(h.Created_Date) BETWEEN date('2021-05-01') AND date('2021-05-01')
 AND A.Vehicle_ID IS NULL
 AND A.DATE IS NULL
AND h.Status IN ('Declined','DECLINED')
ORDER BY Date(h.Created_Date) , ( h.Vehicle_ID)

标签： sqlamazon-web-servicesleft-joinamazon-athena