MySQL SQL 中 2 个选择之间的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7659418/
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
Difference between 2 selects in SQL
提问by M.V.
I have one data table:
我有一个数据表：
--------------------
ID | user | Value
--------------------
1 | 1 | 1
--------------------
2 | 1 | 2
--------------------
3 | 2 | 3
--------------------
4 | 2 | 2
--------------------
5 | 3 | 4
--------------------
6 | 3 | 2
--------------------
I would like to SELECT all rows where value is different comparing to user 1 so the result would be rows with IDs 3 (value is 3) and 5 (value is 2)
我想选择与用户 1 相比值不同的所有行，因此结果将是 ID 为 3（值为 3）和 5（值为 2）的行
I would do something like this (will call it A)
我会做这样的事情（称之为A）
SELECT * FROM table WHERE user = 1
and get all the rows from user 1. Than I would select (will call it B)
并从用户 1 获取所有行。比我会选择（将其称为 B）
SELECT * FROM table WHERE user != 1
and get all other rows. And than I would compare them WHERE A.value != B.value.
并获取所有其他行。而且我会比较它们WHERE A.value != B.value。
I'm stuck on how to merge everything together...
我被困在如何将所有东西合并在一起......
Please help!
请帮忙！
回答by Mahmoud Gamal
Try this:
尝试这个：
SELECT *
FROM table
WHERE value NOT IN ( SELECT value FROM table WHERE user = 1)
回答by onedaywhen
The relational operator is indeed 'difference', Oracle has the keyword MINUS, Standard SQL has the keyword EXCEPTe.g.
关系运算符确实是“差异”，Oracle 有关键字MINUS，标准 SQL 有关键字EXCEPTeg
SELECT value
FROM table
EXCEPT
SELECT value
FROM table
WHERE user = 1;
Sadly, MySQL doesn't have any such an operator, you have to use other SQL constructs e.g. NOT IN <table expression>:
遗憾的是，MySQL 没有任何这样的运算符，您必须使用其他 SQL 结构，例如NOT IN <table expression>：
SELECT value
FROM table
WHERE value NOT IN ( SELECT value
FROM table
WHERE user = 1 );
回答by michael667
select * from table where value not in (select value from table where user = 1);
select * from table where value not in (select value from table where user = 1);
