加入两个表格并从它们两个获得输出

本文介绍了加入两个表格并从它们两个获得输出的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如果这是数据in TestingTable1

  BUYER_ID | ITEM_ID | CREATED_TIME 
 ---------- + ----------------- + ---------------- ------ 
 1345653 151851771618 2012-07-09 19:57:33 
 1345653 110909316904 2012-07-09 21:29:06 
 1345653 221065796761 2012-07-09 19 ：31：48

如果这是TestingTable2中的以下数据

  USER_ID | PRODUCT_ID | LAST_TIME 
 --------- + ---------------- + ------------------ ----- 
 1345653 150851771618 2012-07-09 19:57:33 
 1345653 110909316904 2012-07-09 22:29:06 
 1345653 221165796761 2012-07-09 12： 31:48

我需要比较 TestingTable2 与 TestingTable1 放在 BUYER_ID 和 USER_ID 上。我需要看到，如果 BUYER_ID 和 USER_ID 匹配，那么我需要比较 ITEM_ID 与 PRODUCT_ID 和 CREATED_TIME 与 LAST_TIME 并且如果在与 TestingTable1 中的任何一个或两者进行比较后 TestingTable2 中存在不匹配，则我需要显示结果。

所以，如果你看看上面的例子 - 我有三个场景基本上是：

首先 - 在 TestingTable1 中，第一行 ITEM_ID 与<$不匹配在第一行 TestingTable2 但是 CREATED_TIME 中的c $ c> PRODUCT_ID code> LAST_TIME 作为这两个表中的第一行
其次 - 在 TestingTable1 中，在第二行 CREATED_TIME 与 TestingTable2 第二行中的 LAST_TIME code>但是 ITEM_ID 是ma在<$ li
中为第二行配置 PRODUCT_ID 第三 - 在 TestingTable1 ，第三行 ITEM_ID 与 PRODUCT_ID 以及 CREATED_TIME 不符合 LAST_TIME ，所以在第三行中它们都与 TestingTable1 不匹配第三行。

因此，这三种情况需要在比较 TestingTable2 始终使用 TestingTable1 。并且 TestingTable1 是总是需要进行比较的MAIN表，所以它意味着 TestingTable1 中的数据总是准确的。

因此，如果不考虑上面的例子，如果不匹配它们中的任何一个或两个 - TestingTable1 code> data然后在它旁边有同样的 TestingTable2 数据，这样我就可以看到中有什么值了TestingTable1 相比于 TestingTable2

  BUYER_ID | ITEM_ID | CREATED_TIME | USER_ID | PRODUCT_ID | LAST_TIME 
 ----------- + ----------------- + --------------- ------------ + ---------------- + -------------------- + ----------------------- 
 1345653 151851771618 2012-07-09 19:57:33 1345653 150851771618 2012-07-09 19:57 ：33 
 1345653 110909316904 2012-07-09 21:29:06 1345653 110909316904 2012-07-09 22:29:06 
 1345653 221065796761 2012-07-09 19:31:48 1345653 221165796761 2012- 07-09 12:31:48

于是我写了一个查询，我认为它会覆盖我所有的三种情况，但只包括 First Two 而不是 Third One 。我很疑惑我们是否可以实现第三种情况？

  SELECT * 
 FROM（
 SELECT * 
 FROM TestingTable1 A 
 JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.LAST_TIME = A.Created_TIME 
 WHERE B.PRODUCTID<> A.ITEM_ID 
 UNION ALL 
 SELECT * 
 FROM TestingTable1 A 
 INNER JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.PRODUCTID = A.ITEM_ID 
 WHERE B.t1time< > A.Created_TIME 
）X

任何建议都将不胜感激。

更新： -

只是我最初想要做的快速更新。因为我意识到我的第三种情况存在一些问题。

首先在 TestingTable1 中， ORDER BY）该表由 BUYER_ID 和 CREATED_TIME 并与相同我正在用 USER_ID 和 LAST_TIME 进行排序，并且通过确保数据属于 BUYER_ID 和 USER_ID 。

解决方案

（
从TestingTable1中选择*
A
内连接TestingTable2 B
on A. BUYER_ID = B.USER_ID和
B.LAST_TIME = A.Created_TIME和
B.PRODUCT_ID<> A.ITEM_ID
union all
从TestingTable1中选择*
A.BUYER_ID = B.USER_ID和
的
内部连接TestingTable2 B
B.PRODUCT_ID = A.ITEM_ID和
B.LAST_TIME<> A.CREATED_TIME
）
从C
unio中选择*
n all
从TestingTable1中选择*
A
内部连接TestingTable2 B
上A.BUYER_ID = B.USER_ID和
A.CREATED_TIME<> B.LAST_TIME和
A.ITEM_ID<> B.PRODUCT_ID
如果不存在（从C
中选择*
，其中A.BUYER_ID = C.BUYER_ID和
A.ITEM_ID = C.ITEM_ID和
A. CREATED_TIME = C.CREATED_TIME）和
不存在（从C
中选择*
，其中B.USER_ID = C.USER_ID和
B.PRODUCT_ID = C.PRODUCT_ID和
B.LAST_TIME = C.LAST_TIME）;

If this is the data in TestingTable1

BUYER_ID  |   ITEM_ID       |  CREATED_TIME
----------+-----------------+----------------------
1345653      151851771618     2012-07-09 19:57:33
1345653      110909316904     2012-07-09 21:29:06
1345653      221065796761     2012-07-09 19:31:48

And if this is the below data in TestingTable2

USER_ID  |   PRODUCT_ID    |    LAST_TIME
---------+----------------+-----------------------
1345653     150851771618      2012-07-09 19:57:33
1345653     110909316904      2012-07-09 22:29:06
1345653     221165796761      2012-07-09 12:31:48

I need to compare TestingTable2 with TestingTable1 on BUYER_ID and USER_ID. I need to see, if BUYER_ID and USER_ID gets matched then I need to compare ITEM_ID with PRODUCT_ID and CREATED_TIME with LAST_TIME and if there is a mismatch in TestingTable2 after comparing with TestingTable1 in either one of them or both of them, then I need to show the result.

So if you look at the above example- I have three scenarios basically

Firstly- In TestingTable1, in the First row ITEM_ID is not matching with PRODUCT_ID in the First row of TestingTable2 but CREATED_TIME is matching with LAST_TIME for the first row in both the tables
Secondly- In TestingTable1, in the Second row CREATED_TIME is not matching with LAST_TIME in the second row of TestingTable2 but ITEM_ID is matching with PRODUCT_ID for the second row in both the tables
Thirdly- In TestingTable1, in the Third row ITEM_ID is not matching with PRODUCT_ID and also CREATED_TIME is not matching with LAST_TIME, so in the third row BOTH of them does not match with TestingTable1 third row.

So these are three case that I need to cover while comparing TestingTable2 with TestingTable1 always. And TestingTable1 is the MAIN table through which comparisons need to be made always, so it means data in TestingTable1 is always accurate.

So I need to show the result like this considering the above example if not matching either one of them or both of them- TestingTable1 data then next to it same TestingTable2 data, so that I can see what value was there in TestingTable1 as compared to TestingTable2

BUYER_ID   |   ITEM_ID       |    CREATED_TIME           |      USER_ID   |     PRODUCT_ID     |     LAST_TIME
-----------+-----------------+---------------------------+----------------+--------------------+-----------------------
1345653        151851771618       2012-07-09 19:57:33           1345653        150851771618         2012-07-09 19:57:33
1345653        110909316904       2012-07-09 21:29:06           1345653        110909316904         2012-07-09 22:29:06
1345653        221065796761       2012-07-09 19:31:48           1345653        221165796761         2012-07-09 12:31:48

So I wrote a query, I thought it will cover all my three scenarios, but Only it covered First Two not the Third One. And I am confuse whether we can achieve this third scenario or not?

SELECT *
FROM(
    SELECT *
    FROM TestingTable1 A
    JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.LAST_TIME = A.Created_TIME
    WHERE B.PRODUCTID <> A.ITEM_ID
    UNION ALL
    SELECT *
    FROM TestingTable1 A
    INNER JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.PRODUCTID = A.ITEM_ID
    WHERE B.t1time <> A.Created_TIME
 ) X

Any suggestions will be appreciated.

Update:-

Just a quick update what I was initially thinking to do. As I was aware of few problems with my third scenario.

First of all in TestingTable1, I am sorting(ORDER BY) the table by BUYER_ID and CREATED_TIME and same with TestingTable2 I am sorting with USER_ID and LAST_TIME and I am doing comparison by making sure data belongs to BUYER_ID and USER_ID on a given day.

解决方案

with C as
(
  select *
  from TestingTable1 A
    inner join TestingTable2 B
      on A.BUYER_ID = B.USER_ID and
         B.LAST_TIME = A.Created_TIME and
         B.PRODUCT_ID <> A.ITEM_ID
  union all
  select *
  from TestingTable1 A
    inner join TestingTable2 B
      on A.BUYER_ID = B.USER_ID and
         B.PRODUCT_ID = A.ITEM_ID and
         B.LAST_TIME <> A.CREATED_TIME
)
select *
from C
union all
select *
from TestingTable1 A
  inner join TestingTable2 B
    on A.BUYER_ID = B.USER_ID and
       A.CREATED_TIME <> B.LAST_TIME and
       A.ITEM_ID <> B.PRODUCT_ID
where not exists (select *
                  from C
                  where A.BUYER_ID = C.BUYER_ID and
                        A.ITEM_ID = C.ITEM_ID and
                        A.CREATED_TIME = C.CREATED_TIME) and
      not exists (select *
                  from C
                  where B.USER_ID = C.USER_ID and
                        B.PRODUCT_ID = C.PRODUCT_ID and
                        B.LAST_TIME = C.LAST_TIME);

SQL Fiddle

这篇关于加入两个表格并从它们两个获得输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！