MySQL 如何从MYSQL中的另一个查询结果中减去一个查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6380312/
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
How to subtract a query result from another query result in MYSQL
提问by merlin
1st query
第一个查询
(SELECT a.cat_id,
a.cat_name,
a.cat_description,
b.subcat_name,
b.subcat_description
FROM trade_categories a
LEFT JOIN trade_subcategories b ON a.cat_id = b.cat_id
WHERE a.cat_name LIKE '%catty%'
OR a.cat_description LIKE '%catty%')
UNION
(SELECT c.cat_id,
d.cat_name,
d.cat_description,
c.subcat_name,
c.subcat_description
FROM trade_subcategories c
LEFT JOIN trade_categories d ON c.cat_id = d.cat_id
WHERE c.subcat_name LIKE '%catty%'
OR c.subcat_description LIKE '%catty%')
2nd query :
第二个查询：
SELECT x.cat_id,
x.cat_name,
x.cat_description,
y.subcat_name,
y.subcat_description
FROM trade_categories x
JOIN trade_subcategories y ON x.cat_id = y.cat_id
WHERE ( x.cat_name LIKE '%catty%'
OR x.cat_description LIKE '%catty%' )
AND ( y.subcat_name NOT LIKE '%catty%'
OR y.subcat_description NOT LIKE '%catty%' )
I want to subtract the 2nd query result from 1st query result.
我想从第一个查询结果中减去第二个查询结果。
回答by Bart
http://www.bitbybit.dk/carsten/blog/?p=71
http://www.bitbybit.dk/carsten/blog/?p=71
or example:
或例如：
SELECT Name FROM employee1 WHERE name NOT IN (SELECT name FROM employee2);
回答by Abhay
I think you can either do a NOT IN or a LEFT JOIN. I'd prefer a LEFT JOIN.
我认为您可以执行 NOT IN 或 LEFT JOIN。我更喜欢左连接。
So for example,
例如，
SELECT `QUERY_ONE`.*
FROM `QUERY_ONE`
LEFT JOIN `QUERY_TWO` USING (`cat_id`)
WHERE QUERY_TWO.cat_id IS NULL;
where QUERY_ONE and QUERY_TWO are aliases for your two queries
其中 QUERY_ONE 和 QUERY_TWO 是您的两个查询的别名
回答by Andriy M
SELECT a.cat_id,
a.cat_name,
a.cat_description,
b.subcat_name,
b.subcat_description
FROM trade_categories a
LEFT JOIN trade_subcategories b ON a.cat_id = b.cat_id
WHERE a.cat_name NOT LIKE '%catty%'
AND a.cat_description NOT LIKE '%catty%'
AND (b.cat_id IS NULL
OR b.subcat_name NOT LIKE '%catty%' AND b.subcat_description NOT LIKE '%catty%')
Or, if the results of the two queries have been stored in (temporary) tables, you could use @Abhay's solution on them.
或者，如果两个查询的结果已存储在（临时）表中，您可以对它们使用@Abhay的解决方案。
