替代 MySQL 中的 except
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46872015/
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
Alternative to except in MySQL
提问by Antonio1996
I must write a Query like this in MySQL:
我必须在 MySQL 中编写这样的查询：
SELECT *
FROM Tab1
EXCEPT
SELECT *
FROM Tab1
WHERE int_attribute_of_Tab1>0
but MySQL doesn't support the keyword EXCEPT. Is there a standard modeto use correctly another operator that simulate the except in MySQL?
但 MySQL 不支持关键字EXCEPT。是否有一种标准模式可以正确使用另一个模拟 MySQL 中的 except 的运算符？
回答by Ben Swinburne
You could use NOT IN
你可以使用 NOT IN
SELECT *
FROM Tab1
WHERE id NOT IN (
SELECT id
FROM Tab1
WHERE int_attribute_of_Tab1>0
)
回答by Ryan Gadsdon
Try this
尝试这个
SELECT *
FROM Tab1
WHERE [....] NOT EXISTS
(SELECT *
FROM Tab1
WHERE int_attribute_of_Tab1>0)
回答by P.Salmon
A couple of definitions SqlServer https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sqlEXCEPT Returns any distinct values from the query to the left of the EXCEPT operator that are not also returned from the right query. PLsql https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htmMINUS statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second
SqlServer 的几个定义https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sqlEXCEPT 从查询中返回任何不同的值在 EXCEPT 运算符的左侧，也不会从正确的查询中返回。PLsql https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htmMINUS 语句将结果与 MINUS 运算符组合，该运算符仅返回第一个查询返回的唯一行，而不返回第二个查询返回的唯一行
A pedantic translation to mysql would be
对 mysql 的迂腐翻译是
SELECT distinct t1.*
FROM Tab1 as t1
left outer join
(SELECT *
FROM Tab1
WHERE int_attribute_of_Tab1>0) as t2 on t1.id = t2.id
where t2.id is null;
Assuming there is an id column, And I wouldn't like to use distinct on a lot of columns.
假设有一个 id 列，我不想在很多列上使用 distinct 。
