如何在1个表中选择不存在的数据?

Question

我在这里需要一些sql查询帮助。

我有 1 张表，是 dbo.invoice。

| PO_NO     | TYPE          | MOVEMENT_TYPE     | QUANTITY  | SALESREF  | FLOW  |
|---------- |-----------    |---------------    |---------- |---------- |------ |
| 10001001  | G.RECEIPT     | 101               | 1000.00   | 5001      | S     |
| 10001001  | G.RECEIPT     | 101               | 2000.00   | 5002      | S     |
| 10001001  | G.RECEIPT     | 122               | 1000.00   | 5001      | H     |
| 10001001  | G.RECEIPT     | 122               | 1500.00   | 5002      | H     |
| 10001001  | INVOICE       |                   | 1000.00   | 5001      | S     |
| 10001001  | INVOICE       |                   | 2000.00   | 5002      | S     |
| 10001001  | INVOICE       |                   | 1500.00   | 5002      | H     |

我想要的输出是显示Type = G.RECEIPT，在TYPE=发票上不存在任何数据，SALESREF= 5001和FLOW = H"，它将像这样显示：-

| PO_NO     | TYPE          | MOVEMENT_TYPE     | QUANTITY  | SALESREF  | FLOW  |
|---------- |-----------    |---------------    |---------- |---------- |------ |
| 10001001  | G.RECEIPT     | 122               | 1000.00   | 5001      | H     |

我该怎么做？

更新

这是我当前的查询

SELECT * FROM dbo.INVOICE
WHERE MVT_TYPE = '122' and TYPE NOT IN ( SELECT TYPE FROM DBO.PO where flow ='H' )

Answer 1

use NOT EXIST：

SELECT *
FROM dbo.INVOICE
WHERE SALESREF = 5001 AND FLOW = 'H' AND NOT EXISTS (SELECT 1
FROM dbo.INVOICE
WHERE SALESREF = 5001 AND FLOW = 'H' AND TYPE = 'INVOICE')

对于所有销售参考：

SELECT *
FROM dbo.INVOICE i
WHERE FLOW = 'H' AND NOT EXISTS (SELECT 1
FROM dbo.INVOICE
WHERE FLOW = 'H' AND TYPE = 'INVOICE' AND SALESREF = i.SALESREF)

Answer 2

您可以使用相关的不存在：

SELECT * FROM dbo.INVOICE i
WHERE type='G.RECEIPT'
AND NOT EXISTS(
SELECT * FROM dbo.INVOICE i2
WHERE type='INVOICE'
AND i.PO_NO=i2.PO_NO
AND i.QUANTITY=i2.QUANTITY
AND i.SALESREF=i2.SALESREF
AND i.FLOW=i2.FLOW
)

这样可以更通用地处理需求，以便获取没有匹配发票的所有 G.RECEIPT，而无需对 SALESREF 或任何其他列进行硬编码值。