sql - 如何查找另一个相关行不存在的行

Question

我有一张包含发票号码和交易代码的表格。单张发票可以包含交易代码 10 和 11：

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
|     20           |   CAN000700798  |
+------------------+-----------------+ 

发票可能只包含交易代码为 10 的记录：

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
+------------------+-----------------+ 

显示两种情况的发票表示例：

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
+------------------+-----------------+ 
|     10           |   CAN000900999  |
|     20           |   CAN000900999  |
+------------------+-----------------+ 
|     10           |   CAN000700777  |
|     20           |   CAN000700777  |
+------------------+-----------------+ 

这是我对发票表查询的期望；因为发票 CAN000700798 只包含交易代码 10 而不是 10 和 20。

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
+------------------+-----------------+ 

我需要编写一个查询来查看发票并检查它是否包含具有事务代码 10 和 11 的记录。如果发票仅包含事务代码 10，则返回记录。

标签： sqlexists

Answer 1

您能否尝试以下查询是否适合您：

SELECT T.TransactionCode, T.InvoiceNumber
FROM InvoiceTable AS T
WHERE T.TransactionCode = 10 AND NOT EXISTS (
    SELECT 1
    FROM InvoiceTable AS X
    WHERE X.InvoiceNumber = T.InvoiceNumber AND X.TransactionCode <> 10
)