尝试在 MySQL 中执行 MINUS 操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31397313/
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
Trying to perform MINUS operation in MySQL
提问by Sid
I am trying to perform a MINUSoperation in MySql.I have three tables:
我正在尝试MINUS在 MySql 中执行操作。我有三个表：
- one with service details
- one table with states that a service is offered in
- another table (based on zipcode and state) shows where this service is not offered.
- 一个有服务细节的
- 一张表表明提供服务
- 另一个表格（基于邮政编码和州）显示了不提供此服务的地方。
I am able to get the output for those two select queries separately. But I need a combined statement that gives the output as 'SELECT query_1 - SELECT query_2'.
我能够分别获得这两个选择查询的输出。但我需要一个组合语句，将输出作为“SELECT query_1 - SELECT query_2”。
Service_Details TableService_Code(PK) Service Name
Service_Details 表Service_Code(PK) 服务名称
Servicing_States TableService_Code(FK) State Country PK(Service_Code,State,Country)
Servicing_States 表Service_Code(FK) State Country PK(Service_Code,State,Country)
Exception TableService_Code(FK) Zipcode State PK(Service_Code,Zipcode,State)
异常表Service_Code(FK) 邮政编码状态 PK(Service_Code,Zipcode,State)
回答by Mahipal
MySql does not recognise MINUSand INTERSECT, these are Oracle based operations. In MySql a user can use NOT INas MINUS(other solutions are also there, but I liked it lot).
Example:
MySql 不识别MINUS和INTERSECT，这些是基于 Oracle 的操作。在 MySql 中，用户可以使用NOT INas MINUS（其他解决方案也有，但我很喜欢它）。例子：
select a.id
from table1 as a
where <condition>
AND a.id NOT IN (select b.id
from table2 as b
where <condition>);
回答by Ajay Raturi
MySQL Does not supports MINUS or EXCEPT,You can use NOT EXISTS , NULL or NOT IN.
MySQL 不支持 MINUS 或 EXCEPT，您可以使用 NOT EXISTS 、NULL 或 NOT IN。
回答by Jose Manuel Gomez Alvarez
Here's my two cents... a complex query just made it work, originally expressed with Minus and translated for MySql
这是我的两分钱……一个复杂的查询使它起作用，最初用减号表示并为 MySql 翻译
With MINUS:
带减号：
select distinct oi.`productOfferingId`,f.name
from t_m_prod_action_oitem_fld f
join t_m_prod_action_oitem oi
on f.fld2prod_action_oitem = oi.oid;
minus
select
distinct r.name,f.name
from t_m_prod_action_oitem_fld f
join t_m_prod_action_oitem oi
on f.fld2prod_action_oitem = oi.oid
join t_m_rfs r
on r.name = oi.productOfferingId
join t_m_attr a
on a.attr2rfs = r.oid and f.name = a.name;
With NOT EXISTS
不存在
select distinct oi.`productOfferingId`,f.name
from t_m_prod_action_oitem_fld f
join t_m_prod_action_oitem oi
on f.fld2prod_action_oitem = oi.oid
where not exists (
select
r.name,f.name
from t_m_rfs r
join t_m_attr a
on a.attr2rfs = r.oid
where r.name = oi.productOfferingId and f.name = a.name
回答by spencer7593
An anti-join pattern is the approach I typically use. That's an outer join, to return all rows from query_1, along with matching rows from query_2, and then filtering out all the rows that had a match... leaving only rows from query_1 that didn't have a match. For example:
反连接模式是我通常使用的方法。这是一个外连接，返回来自 query_1 的所有行，以及来自 query_2 的匹配行，然后过滤掉所有匹配的行......只留下来自 query_1 的没有匹配的行。例如：
SELECT q1.*
FROM ( query_1 ) q1
LEFT
JOIN ( query_2 ) q2
ON q2.id = q1.id
WHERE q2.id IS NULL
To emulate the MINUSset operator, we'd need the join predicate to compare all columns returned by q1 and q2, also matching NULL values.
为了模拟MINUS集合运算符，我们需要连接谓词来比较 q1 和 q2 返回的所有列，也匹配 NULL 值。
ON q1.col1 <=> q2.col2
AND q1.col2 <=> q2.col2
AND q1.col3 <=> q2.col3
AND ...
Also, To fully emulate the MINUSoperation, we'd also need to remove duplicate rows returned by q1. Adding the DISTINCTkeyword would be sufficient to do that.
此外，为了完全模拟MINUS操作，我们还需要删除 q1 返回的重复行。添加DISTINCT关键字就足以做到这一点。
回答by PressingOnAlways
The tables have to have the same columns, but I think you can achieve what you are looking for with EXCEPT... except that EXCEPTonly works in standard SQL! Here's how to do it in MySQL:
这些表必须具有相同的列，但我认为您可以使用EXCEPT...实现您正在寻找的内容……除了EXCEPT仅适用于标准 SQL！以下是在 MySQL 中执行此操作的方法：
SELECT * FROM Servicing_states ss WHERE NOT EXISTS
( SELECT * FROM Exception e WHERE ss.Service_Code = e.Service_Code);
http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
Standard SQL
标准 SQL
SELECT * FROM Servicing_States
EXCEPT
SELECT * FROM Exception;
