SQL 通过sem选择两个表之间不匹配的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14554732/
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
Select rows with no match between two tables by sem
提问by leschandrew
I have two tables, Table1 and Table2. I want to select distinct rows in Table2 that are not contained in Table1. Here is an example:
我有两个表，Table1 和 Table2。我想选择 Table2 中未包含在 Table1 中的不同行。下面是一个例子：
Table1
| A | | sem|
------------------
1. | aa | | 1 |
---------------
2. | bb | | 1 |
----------------
3. | aa | | 2 |
-----------------
4. | cc | | 2 |
---------------
Table2
| B |
------
1. | aa |
------
2. | aa |
------
3. | bb |
------
4. | cc |
------
5. | cc |
------
6. | ee |
------
7. | ee |
------
I would want the those row who is not common in this two table when sem = 1 like output for sem = 1 result
当 sem = 1 时，我希望在这两个表中不常见的那些行像 sem = 1 结果的输出
| B |
| 乙 |
- | cc |
- | ee |
- | 抄送 |
- | ee |
回答by user798774
You could try something like this.
你可以尝试这样的事情。
SELECT B
FROM Table2 b
WHERE NOT EXISTS (
SELECT *
FROM Table1 a
WHERE a.A = b.B)
From what I can gather, the reason why yours isn't working is because your getting all the values that ARE in both table1 and table2. What you should be doing is like what I did above. Get all the values that are in both tables and check which values in both tables are NOT within the result set, that is why i have set up a sub-query. I'm a little rusty on my SQL so take what i've said with a gain of salt.
据我所知，您的不起作用的原因是因为您获得了 table1 和 table2 中的所有值。你应该做的就像我上面所做的一样。获取两个表中的所有值并检查两个表中的哪些值不在结果集中，这就是我设置子查询的原因。我对我的 SQL 有点生疏，所以请听我说的加盐。
回答by Gordon Linoff
You can do this with an outer joinand a whereclause. In this case, a right outer join, I think:
你可以用一个outer join和一个where子句来做到这一点。在这种情况下，我认为是右外连接：
SELECT cd.cGenotype
FROM dbo.TestResults tr right outer join
dbo.CombinedData cd
ON (tr.TestResult = cd.cGenotype)
where tr.testresult is null
GROUP BY cd.cGenotype
回答by Bassam Mehanni
SELECT DISTINCT b.cGenotype
FROM dbo.CombinedData b
WHERE NOT EXISTS (SELECT *
FROM dbo.TestResults a
WHERE a.TestResult = b.cGenotype)
