MySQL 如何比较两个相同表中的行并返回缺失行的结果集？

How to compare rows in two identical tables and return result set of missing rows?

提问by EmmyS

I've looked through all the related questions, but none of them does exactly what I need. I have two identically-structured tables (id, VoucherNbr, BalanceInit) - one is from our current production system (tableA), and the other is from a client's legacy system (tableB). Table A has records a,b,c,d,e (for example), and table B has a,b,c,d,e,f,g. Table B will always have all the values that exist in table A, but also has extra rows. I need to return a resultset that contains all the rows in table B that don't exist in table A (f and g, using the example.) How can I do this?

我已经浏览了所有相关的问题，但没有一个完全符合我的需要。我有两个结构相同的表（id、VoucherNbr、BalanceInit）——一个来自我们当前的生产系统（tableA），另一个来自客户的遗留系统（tableB）。表 A 有记录 a、b、c、d、e（例如），表 B 有 a、b、c、d、e、f、g。表 B 将始终包含表 A 中存在的所有值，但也有额外的行。我需要返回一个结果集，其中包含表 B 中不存在于表 A 中的所有行（f 和 g，使用示例）。我该怎么做？

EDIT:

编辑：

TABLE A

表A

ID | VoucherNbr         | BalanceInit
=============================================
1  | 1111111111111111   | 25.00
2  | 2222222222222222   | 50.00
3  | 3333333333333333   | 10.00

TABLE B

表B

ID | VoucherNbr         | BalanceInit
=============================================
15 | 1111111111111111   | 25.00
17 | 1212121212121212   | 15.00
22 | 2222222222222222   | 50.00
34 | 3333333333333333   | 25.00
41 | 3232323232323232   | 75.00

What I need to get back is a resultset containing only the rows that don't exist in Table A, where it's the value in the VoucherNbr field that doesn't exist, not the ID field.

我需要取回的是只包含表 A 中不存在的行的结果集，其中 VoucherNbr 字段中不存在的值，而不是 ID 字段。

ID | VoucherNbr         | BalanceInit
=============================================
17 | 1212121212121212   | 15.00
41 | 3232323232323232   | 75.00

回答by Larry Lustig

Some databases offer this feature directly using a SUBTRACT or MINUS operation. MySQL doesn't seem to have any such operation.

某些数据库使用 SUBTRACT 或 MINUS 操作直接提供此功能。MySQL 似乎没有任何此类操作。

I would probably do this:

我可能会这样做：

SELECT  B.id, B.voucherNbr, B.balanceInit FROM tableB B
   WHERE NOT EXISTS 
     (SELECT * FROM tableA A 
      WHERE A.voucherNbr = B.voucherNbr AND A.balanceInit = B.balanceInit)

However, here are a couple of other MySQL solutions (I googled them) you can test for speed:

但是，这里有一些其他 MySQL 解决方案（我用谷歌搜索它们），您可以测试速度：

SELECT  id, voucherNbr, balanceInit FROM tableB 
   WHERE (voucherNbr, balanceInit) NOT IN 
      (SELECT voucherNbr, balanceInit FROM tableA);

SELECT  id, voucherNbr, balanceInit
   FROM tableB LEFT JOIN tableA USING (voucherNbr, balanceInit)
      WHERE tableA.voucherNbr IS NULL

Honestly, I like the first googled solution better than my own if the execution time is similar or better.

老实说，如果执行时间相似或更好，我更喜欢第一个 googled 解决方案。

Note: I've modified these from my original answer to reflect the fact that you state in a comment to another answer that the ids vary in the two tables.

注意：我已经从我的原始答案中修改了这些内容，以反映您在另一个答案的评论中声明两个表中的 id 不同的事实。

回答by Jagmag

Note: Assuming idis the PK for both tables.

注意：假设id是两个表的 PK。

SELECT B.* FROM TableB B 
LEFT OUTER JOIN TableA A
ON B.id = A.id
WHERE A.id IS NULL

EDIT : Based on further commments from OP, since Id's are different for the same rows in the two tables, assuming that the matching criteria is based on comparing the 2 column values

编辑：基于来自 OP 的进一步评论，因为两个表中相同行的 Id 不同，假设匹配标准基于比较 2 列值

SELECT B.* FROM TableB B 
LEFT OUTER JOIN TableA A
ON B.VoucherNbr = A.VoucherNbr
AND B.BalanceInit = A.BalanceInit
WHERE A.VoucherNbr IS NULL 

EDIT2 : If only voucher number itself is sufficient, then balanceInit too can be discarded

EDIT2 ：如果只有凭证号本身就足够了，那么 balanceInit 也可以被丢弃

SELECT B.* FROM TableB B 
LEFT OUTER JOIN TableA A
ON B.VoucherNbr = A.VoucherNbr
WHERE A.VoucherNbr IS NULL 

回答by Sammy

You can use INTERSECTstament

您可以使用INTERSECT语句

SELECT * FROM tableA 
INTERSECT 
SELECT * FROM tableB 

-EXCEPT returns any distinct values from the left query that are not also found on the right query.

-EXCEPT 返回左侧查询中未在右侧查询中找到的任何不同值。

-INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

-INTERSECT 返回查询在 INTERSECT 操作数的左侧和右侧返回的任何不同值。

http://msdn.microsoft.com/en-us/library/ms188055.aspx

http://msdn.microsoft.com/en-us/library/ms188055.aspx