SQL 查询不存在 [英] SQL Query Not Exist

问题描述

我有 2 个表，Main 和 Payments.Main 是一张交易表，Payments 是一张表，其中的交易为其他交易支付.

I have 2 tables, Main and Payments. Main is a table of transactions, Payments is a table of which transactions pay for other transactions.

Main 有很多字段，例如 Invo、InvoDate 和 Amount.

Main has many fields, like Invo, InvoDate, and Amount.

付款非常简单；它只有 PayInvo 和 DueInvo.[和PK]

Payments is very simple; it just has PayInvo and DueInvo. [and a PK]

我必须弄清楚哪些是在 2019 年底之前仍未付款的.我的方法如下.[Main.Ac1 是账户.所有应付账款都在 2000 年至 2999 年之间]

I have to figure out which what was still pending payment by the end of 2019. My approach was as follows below. [Main.Ac1 is the account. All accounts payable are between 2000 and 2999]

SELECT * FROM Main 
WHERE InvoDate BETWEEN #1/1/2019# AND #12/31/2019# 
AND Main.Ac1 BETWEEN 2000 AND 2999 
AND NOT EXISTS 
( 
SELECT * FROM 
Payments INNER JOIN Main ON Payments.PayInvo = Main.Invo 
WHERE Main.InvoDate BETWEEN #1/1/2019# AND #12/31/2019#
);

我希望它只返回一个交易列表，其中 Main.Ac1 在 2000 年和 2999 年之间，并且相应的付款日期不是 2019 年，但它什么也没带回来.

I expected it to ONLY return a list of transactions where the Main.Ac1 BETWEEN 2000 AND 2999 and also where a corresponding payment was NOT dated 2019, but it brings back nothing.

推荐答案

您的问题是您的 NOT EXISTS 子句中的子查询与您的主查询无关，因此它总是返回一个结果 (只要 Payments) 中有任何行，NOT EXISTS 就会返回 false 并且您的查询中没有任何行.您可以通过不 JOIN 到 Main 而是引用外部查询中的 Main 表来关联子查询:

Your problem is that the subquery in your NOT EXISTS clause is not correlated to your main query, so it always returns a result (as long as there are any rows in Payments) and so NOT EXISTS returns false and you get no rows from your query. You can correlate the subquery by not JOINing to Main but instead referring to the Main table from the outer query:

SELECT * 
FROM Main 
WHERE InvoDate BETWEEN #1/1/2019# AND #12/31/2019# 
  AND Main.Ac1 BETWEEN 2000 AND 2999 
  AND NOT EXISTS 
  ( 
    SELECT *
    FROM Payments
    WHERE Payments.PayInvo = Main.Invo 
  )

请注意，子查询中不需要日期测试，因为它已包含在外部查询中.

Note you shouldn't need the date test in the subquery as it is covered by the outer query.

这篇关于SQL 查询不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！