如何比较两个表 [英] How to compare two tables

问题描述

考虑以下两个表格。

表A

Consider the below two tables.
TABLE A

Order  invoice  date       quantity  cust_code
111     45     20/08/2017   9          abc
112     32     21/08/2017   4          abc
113     42     22/08/2017   5          abc
114     49     20/08/2017   5          abc

表B

TABLE B

Order   date       quantity
 111     20/08/2017   3
 111     20/08/2017   6
 113     22/08/2017   5
 49      20/08/2017   5
 112     21/08/2017   4

现在在表B中，Order列中的值可以是表A中的order列，也可以是表A中的Invoice。我想以这样的方式连接表，即我在表B中添加缺少的列来自表A.



预期结果

Now in table B the values in Order column can either be the order column from table A or the Invoice from table A. I want to join the tables in such a way that i add the missing columns in table B from table A.

Expected result

Order   date       quantity  Invoice
 111     20/08/2017   3       45
 111     20/08/2017   6       45
 113     22/08/2017   5       42
 49      20/08/2017   5       49
 112     21/08/2017   4       32

我尝试过：

What I have tried:

select * 
from Table B  d2
left join Table A d1 on d2.Invoice =d1.order 

这只匹配订单栏。所以在期望结果表第4行，在发票栏中它会说空，我不希望这样。如果订单栏不匹配，那么我希望它检查发票栏。

This only matches the order column. So in expect result table Row 4, in invoice column it will say null, i do not want that. if the order column does not match then i want it to check the invoice column.

推荐答案

所以为你的JOIN条件添加一个标准：

So add a criteria to your JOIN condition:

SELECT b.[Order], b.[Date],  b.Quantity, a.Invoice
FROM TableB  b
LEFT JOIN TableA a ON b.[Order] = a.[order] OR b.[Order] = a.Invoice

这篇关于如何比较两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！