如何比较 MySQL 中两个查询结果的相等性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6627070/
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 compare two query results for equality in MySQL?
提问by Gwen Avery
I'm a MySQL user. I have two queries, and I wish to compare their results for equality. I would like to do this with a single query which would return true or false, so each of my two queries would most likely take the form of sub-queries.
我是 MySQL 用户。我有两个查询，我希望比较它们的结果是否相等。我想用一个返回 true 或 false 的查询来做到这一点，所以我的两个查询中的每一个都很可能采用子查询的形式。
I would like to avoid returning the results from both queries and comparing them at the application level, to cut down on communication and to improve performance. I would also like to avoid looping over the results at the database level if possile, but if there's no other way, so be it.
我想避免从两个查询返回结果并在应用程序级别比较它们，以减少通信并提高性能。如果可能的话，我还想避免在数据库级别循环结果，但如果没有其他方法，就这样吧。
I have searched high and low for an example on how to do this, but have come up empty handed. Some sample codewould be most appreciated, because I'm a newbie to SQL programming. Thanks!
我已经搜索了很多关于如何做到这一点的例子，但空手而归。一些示例代码将非常受欢迎，因为我是 SQL 编程的新手。谢谢！
Note:I'm looking for a solution which would work with any two arbitrary queries, so I'm going to refrain from posting what my two queries happen to be.
注意：我正在寻找一种可以处理任意两个任意查询的解决方案，因此我将避免发布我的两个查询恰好是什么。
回答by MatBailie
SELECT
CASE WHEN count1 = count2 AND count1 = count3 THEN 'identical' ELSE 'mis-matched' END
FROM
(
SELECT
(SELECT COUNT(*) FROM <query1>) AS count1,
(SELECT COUNT(*) FROM <query2>) AS count2,
(SELECT COUNT(*) FROM (SELECT * FROM query1 UNION SELECT * FROM query2) AS unioned) AS count3
)
AS counts
回答by Conrad Frix
This would be a little easier if MySQL supported FULL OUTER JOINalso note that if the the two queries give the same results but in different order that will be deemed equivlant
如果 MySQL 支持，这会更容易一些FULL OUTER JOIN还请注意，如果两个查询给出相同的结果但顺序不同，则将被视为等效
SELECT
COUNT(*)
FROM
(
(SELECT A, b, c FROM A) a
LEFT OUTER JOIN
(SELECT A, b, c FROM b) B
ON A.a = b.a and a.b = b.b and a.c = b.c
UNION
(SELECT A, b, c FROM A) a
RIGHT OUTER JOIN
(SELECT A, b, c FROM b) B
ON A.a = b.a and a.b = b.b and a.c = b.c
)
WHERE a.a is null or b.a is null
If the count =0 then the two queries are the same
如果计数=0，则两个查询相同
Also because I'm using UNION duplicates are being removed. So there's a potential inaccuracy there.
也因为我正在使用 UNION 重复项被删除。所以这里有一个潜在的不准确之处。
回答by Phil
You can't do MINUS in MySQL, so here's how to do it without:
你不能在 MySQL 中做 MINUS，所以这里是如何做到的：
select if(count(*)=0,'same','different') from (
select col1,col2,col3
from tableone
where ( col1, col2, col3 ) not in
( select col4, col5, col6
from tabletwo )
union
select col4, col5, col6
from tabletwo
where ( col4, col5, col6 ) not in
( select col1, col2, col3
from tableone )
) minusintersec;
That's given:
这是给的：
tableone (col1 integer, col2 integer, col3 integer );
tabletwo (col4 integer, col5 integer, col6 integer );
