使用 SQL 查找不匹配的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/508509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding unmatched records with SQL
提问by J.T. Grimes
I'm trying write a query to find records which don't have a matching record in another table.
我正在尝试编写一个查询来查找在另一个表中没有匹配记录的记录。
For example, I have a two tables whose structures looks something like this:
例如，我有两个表，其结构如下所示：
Table1
State | Product | Distributor | other fields
CA | P1 | A | xxxx
OR | P1 | A | xxxx
OR | P1 | B | xxxx
OR | P1 | X | xxxx
WA | P1 | X | xxxx
VA | P2 | A | xxxx
Table2
State | Product | Version | other fields
CA | P1 | 1.0 | xxxx
OR | P1 | 1.5 | xxxx
WA | P1 | 1.0 | xxxx
VA | P2 | 1.2 | xxxx
(State/Product/Distributor together form the key for Table1. State/Product is the key for Table2)
（State/Product/Distributor 共同构成 Table1 的 key。State/Product 是 Table2 的 key）
I want to find all the State/Product/Version combinations which are Not using distributor X. (So the result in this example is CA-P1-1.0, and VA-P2-1.2.)
我想找到所有不使用分销商 X 的状态/产品/版本组合。（所以这个例子中的结果是 CA-P1-1.0 和 VA-P2-1.2。）
Any suggestions on a query to do this?
关于执行此操作的查询的任何建议？
回答by gbn
SELECT
*
FROM
Table2 T2
WHERE
NOT EXISTS (SELECT *
FROM
Table1 T1
WHERE
T1.State = T2.State AND
T1.Product = T2.Product AND
T1.Distributor = 'X')
This should be ANSI compliant.
这应该符合 ANSI。
回答by mwigdahl
In T-SQL:
在 T-SQL 中：
SELECT DISTINCT Table2.State, Table2.Product, Table2.Version
FROM Table2
LEFT JOIN Table1 ON Table1.State = Table2.State AND Table1.Product = Table2.Product AND Table1.Distributor = 'X'
WHERE Table1.Distributor IS NULL
No subqueries required.
不需要子查询。
Edit: As the comments indicate, the DISTINCT is not necessary. Thanks!
编辑：正如评论所示， DISTINCT 不是必需的。谢谢！
回答by Rockcoder
SELECT DISTINCT t2.State, t2.Product, t2.Version
FROM table2 t2
JOIN table1 t1 ON t1.State = t2.State AND t1.Product = t2.Product
AND t1.Distributor <> 'X'
回答by Quassnoi
In Oracle:
在甲骨文中：
SELECT t2.State, t2.Product, t2.Version
FROM Table2 t2, Table t1
WHERE t1.State(+) = t2.State
AND t1.Product(+) = t2.Product
AND t1.Distributor(+) = :distributor
AND t1.State IS NULL
回答by Tundey
select * from table1 where state not in (select state from table1 where distributor = 'X')
select * from table1 where state not in（从 table1 中选择状态，其中分发者 = 'X'）
Probably not the most clever but that should work.
可能不是最聪明的，但这应该有效。
