减号运算符在 mysql 中给我错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12128844/
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
Minus operator giving me erros in mysql
提问by user1529342
I have two queries.
我有两个疑问。
First query returning 11 rows and second query returning 6 rows when i use the minus operator on them it should return 5 rows as far as my understanding
当我对它们使用减号运算符时，第一个查询返回 11 行，第二个查询返回 6 行，据我所知它应该返回 5 行
SELECT location from uploads where username='Gates'
MINUS
SELECT fileshare FROM `whiteboard` where username='Gates' and friend='Curlyclouds'
But i am getting the following error:
但我收到以下错误：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'minus SELECT fileshare FROM
whiteboardwhere username='Gates' and friend='Cur' at line 2
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以
whiteboard在第 2 行的“减去 SELECT fileshare FROM where username='Gates' andfriend='Cur'”附近使用正确的语法
Hope my question is clear and any help would be helpful to me .....Thank You
希望我的问题很清楚，任何帮助都会对我有所帮助.....谢谢
回答by Martin Smith
MySQL does not support EXCEPTor MINUS.
MySQL 不支持EXCEPT或MINUS.
You can use NOT EXISTS, OUTER JOIN ... NULLor NOT IN(be careful of NULLs) to do an anti semi join.
您可以使用NOT EXISTS,OUTER JOIN ... NULL或NOT IN（小心 NULL）来执行反半连接。
回答by Allen Shatzer
Using a "not in" or "not exists" to perform a "minus" query on very large data sets can result in extremely long query times. I came up with a method that mimics the set based operations performed by other databases (merge, sort, remove duplicates).
使用“不在”或“不存在”对非常大的数据集执行“减”查询可能会导致查询时间过长。我想出了一种方法来模拟其他数据库执行的基于集合的操作（合并、排序、删除重复项）。
select column1, count(*), min(setnum)
from
(
select distinct column1, 1 as setnum
from table1
union all
select distinct column1, 2 as setnum
from table2
) as tbl_a
group by column1
having count(*) = 1 and min(setnum) = 1
The above select yields very good performance on large data sets vs the use of not exists or not in. Essentially, it is looking for rows that only exist in the first set and not in the second. I have used this quite often lately with very good success with respect to performance.
与使用不存在或不存在相比，上面的选择在大型数据集上产生了非常好的性能。本质上，它正在寻找只存在于第一个集中而不存在于第二个集中的行。我最近经常使用它，在性能方面取得了非常好的成功。
