多加入在单个SQL查询(Multi Join in a single SQL query)

下面是始终降序按日期排序在TestingTable1数据

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

如果这是在TestingTable2下面的数据按日期总是降序排列

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

在每一行TestingTable1应与TestingTable2 ，如果不匹配或数据不存在在TestingTable2，那么我需要向他们展示在输出中TestingTable1我有这个数据，但对应TestingTable2我有这样的数据（这将是错误的数据），这样我可以看到什么是错配和丢失的数据。

我需要比较TestingTable2与TestingTable1上BUYER_ID和USER_ID 。 我需要看到，如果BUYER_ID和USER_ID被匹配的话，我需要比较ITEM_ID与PRODUCT_ID和CREATED_TIME有LAST_TIME ，如果存在不匹配TestingTable2与比较后TestingTable1在他们或他们两人中的任何一个，那么我需要证明结果。

所以，如果你看一下上面的示例 - 我有三个基本的场景

1. Firstly-在TestingTable1 ，在第一行ITEM_ID与匹配PRODUCT_ID的第一行TestingTable2但CREATED_TIME不匹配LAST_TIME在两个表的第一行
2. Secondly-在TestingTable1 ，在第二行中CREATED_TIME与匹配LAST_TIME的第二行中TestingTable2但ITEM_ID不与匹配PRODUCT_ID在两个表的第二行
3. Thirdly-在TestingTable1 ，最后两行（行）是不是有在所有TestingTable2 。 此方案不涉及我查询我写的。 我想这种情况下也是在我的查询 。

因此，这些都是这三种情况下，我需要在比较覆盖TestingTable2与TestingTable1始终。 而TestingTable1是主表通过比较总要成为做，所以这意味着在数据TestingTable1总是准确的。

所以，我需要证明像下面考虑上面的例子，如果不符合任何一方或数据的一个不存在的结果TestingTable2在清一色TestingTable1数据，然后在它旁边同样TestingTable2数据，这样我可以看到什么样的价值是存在的TestingTable1相比TestingTable2

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

下面是我写的查询只包括我的two scenarios ，我上面提到的，它工作正常，我会得到输出像上面从输出，保留最后两行。 但我需要添加third scenario也是在这（下）查询，以便它使输出像上面。

SELECT *
FROM   (SELECT T2.buyer_id,
               T2.item_id,
               T2.created_time AS created_time,
               subq.user_id,
               subq.product_id,
               subq.LAST_TIME
        FROM   TestingTable2 subq
               JOIN TestingTable1 T2
                 ON T2.buyer_id = subq.user_id
                    AND subq.LAST_TIME = ( T2.created_time )
        WHERE  ( subq.product_id <> T2.item_id )
        UNION ALL
        SELECT T2.buyer_id,
               T2.item_id AS item_id,
               T2.created_time,
               subq.user_id,
               subq.product_id AS product_id,
               subq.LAST_TIME
        FROM   TestingTable2 subq
               JOIN TestingTable1 T2
                 ON T2.buyer_id = subq.user_id
                    AND subq.product_id = T2.item_id
        WHERE  ( subq.LAST_TIME <> ( T2.created_time ) )) finalResult
ORDER  BY finalResult.BUYER_ID;

任何建议将受到赞赏。

PS我已经要求在过去的几天里加入相关的几个问题，但只覆盖我的两个场景，没有第三种情况，我需要在此查询。

更新： -我不能使用NOT IN或NOT EXISTS语法SQL，因为我与蜂巢工作，蜂房不支持NOT IN或NOT EXISTS说，所以我需要一些其他的方式来解决这个问题。

我需要用我的查询只修改为第三个方案工作，因为这样蜂房支持SQL语法。

下面是我的SQL小提琴 ，即满足上述但不是我的两个方案中的第三个方案。 谁能帮我修改我的查询，为第三个方案的工作呢？

http://sqlfiddle.com/#!3/102dd/1/0 。

在表1的数据应该是有表2中，如果它不存在，那么我需要从表1的比较，也有可能，从表1的数据不会在表2在那里后，显示的数据之间的不匹配，我想证明太。

对于小提琴更新的输出http://sqlfiddle.com/#!3/102dd/3/0

BUYER_ID   |   ITEM_ID       |    CREATED_TIME           |      USER_ID   |     PRODUCT_ID     |     LAST_TIME   
-----------+-----------------+---------------------------+----------------+--------------------+-----------------------
1345653       151851771618      July, 09 2012 19:57:33      1345653            150851771618         July, 09 2012 19:57:33
1345653       221065796761      July, 09 2012 19:31:48      1345653            221165796761         July, 09 2012 19:31:48
1345653       110909316904      July, 09 2012 21:29:06      1345653            110909316904         July, 09 2012 22:29:06
1345653       400307563710      July, 09 2012 18:57:33      NULL               NULL                 NULL
1345653       310411560125      July, 09 2012 16:09:49      NULL               NULL                 NULL

更新后的SQL查询是给我的错误

我换成TestingTable1这个查询-

(SELECT BUYER_ID, ITEM_ID, rank(BUYER_ID), CREATED_TIME
FROM (
    SELECT BUYER_ID, ITEM_ID, CREATED_TIME
    FROM testingtable1
    where to_date(from_unixtime(cast(UNIX_TIMESTAMP(CREATED_TIME) as int))) = '2012-07-09'
    DISTRIBUTE BY BUYER_ID
    SORT BY BUYER_ID, CREATED_TIME desc
) T1
WHERE rank(BUYER_ID) < 5)

而TestingTable2与此查询-

(SELECT USER_ID, PROD_AND_TS.PRODUCT_ID as PRODUCT_ID, PROD_AND_TS.TIMESTAMPS as TIMESTAMPS FROM testingtable2 lateral view explode(PURCHASED_ITEM) exploded_table as PROD_AND_TS where to_date(from_unixtime(cast(PROD_AND_TS.TIMESTAMPS as BIGINT))) = '2012-07-09')

Answer 1:

如果你不能使用NOT IN或张贴在干净的解决方案@eggyal因为DBMS的限制，另一个选择可能是完全复制原来的工会，并使用在那些结果LEFT JOIN 。

适用于您的sqlFiddle ，下面的语句返回所需的结果。

SQL语句

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.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  
    WHERE B.LAST_TIME <> A.Created_TIME      
 ) X
UNION ALL
SELECT A.*, NULL, NULL, NULL
FROM   TestingTable1 A
       LEFT OUTER JOIN (
            SELECT *
            FROM TestingTable1 A
            JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.LAST_TIME = A.Created_TIME 
            WHERE 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  
            WHERE B.LAST_TIME <> A.Created_TIME      
       ) X ON A.BUYER_ID = X.BUYER_ID AND A.ITEM_ID = X.ITEM_ID
WHERE  X.BUYER_ID IS NULL

Answer 2:

试试这段代码，写的SQL。 我已经测试了这个在SQL提琴。

 SELECT 
 tt1.buyer_id,tt1.item_id,tt1.created_time,
 tt2.user_id,tt2.product_id,tt2.last_time
 FROM 
 testingtable1 tt1 LEFT OUTER JOIN
 testingtable2 tt2 ON
 tt1.buyer_id = tt2.user_id
 AND 
 tt1.item_id = tt2.product_id
 AND
 tt1.created_time = tt2.last_time

Answer 3:

这个答案是响应您的评论的要求所以在这里做的事： https://stackoverflow.com/a/11440651/1166147 。
其实，我已经给你在其他副本的2创建精确的输出你是显示这个问题的答案。 这是第一个查询我写在这里： https://stackoverflow.com/a/11440539/1166147并提到在这里两次解释说： https://stackoverflow.com/a/11398990/1166147

我修改您的查询返矿，并在您SQLFiddle运行它：

您的要求已演变，虽然，它看起来像你可以现在只是做一个LEFT JOIN如果TestingTable1真的是永远准确的数据。

SELECT *
FROM TestingTable1 A
LEFT JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND (B.LAST_TIME = A.Created_TIME  OR B.PRODUCT_ID = A.ITEM_ID)

---

编辑发表评论

MOCK情景

SCENARIO||       TABLE1           ||           TABLE2
----------------------------------------------------------
SCENARIO||  BUYER  ITEM      TIME ||  USER   PRODUCT  TIME
1       ||    1     A        09   ||   1     Z        09
2       ||    1     B        10   ||   NO RECORD IN TABLE 2
3       ||    1     C        10   ||   1     C        02
4       ||    1     D        12   ||   NO RECORD IN TABLE 2
5       ||    1     E        01   ||   1     E        01
6       ||   NO RECORD IN TABLE 1 ||   1     Y        05      

你问一个SQL解决方案 - 但它是不是一个真正的SQL问题。 您排除给你，因为你不能在你的环境中使用它们，然后再重新打开同一个问题的有效的SQL答案。 这是一个HQL / HIVE的问题。 是的，有一些相似之处，但也有一些差异。

它看起来像HQL会支持这样的事情，虽然我没有办法测试和SQLFiddle不是有效期为测试这一点。 你将不得不解决如何做ISNULL或COALESCE在HQL，在选择旁边的注释*如图所示。 这将“合并”的结果，并返回从哪个是不为空，如果有一个非空值存在的价值。 我相信HQL支持ISNULL：

select * --BUYER_ID, isNull(B.USER_ID,C.USER_ID)
from
(select BUYER_ID,ITEM_ID ,Created_TIME  from TestingTable1) a
left join
(SELECT USER_ID,PRODUCT_ID, last_time
  FROM TestingTable2 ) b on(a.BUYER_ID = b.user_id and B.last_time =   A.Created_TIME)
left join
(SELECT USER_ID,PRODUCT_ID, last_time
  FROM TestingTable2 ) c on(a.BUYER_ID = c.user_id and c.PRODUCT_ID = A.ITEM_ID)

这里是另一个TSQL的答案，可能会给你一些想法。 我从来没有在TSQL使用这一点，但它会实际工作。

SELECT A.BUYER_ID,A.ITEM_ID,CREATED_TIME,COALESCE(B.USER_ID,X.USER_ID,Y.USER_ID),
COALESCE(B.PRODUCT_ID,X.PRODUCT_ID,Y.PRODUCT_ID)
,COALESCE(B.last_time,X.last_time,Y.last_time)
FROM TestingTable1 A
LEFT JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.PRODUCT_ID = A.ITEM_ID
AND B.last_time = A.Created_TIME
LEFT JOIN( SELECT USER_ID,PRODUCT_ID, last_time
  FROM TestingTable1 A
  LEFT JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.last_time = A.Created_TIME 
  WHERE  ISNULL(B.PRODUCT_ID,0) <> A.ITEM_ID AND B.USER_ID IS NOT NULL) X ON
  X.USER_ID = A.BUYER_ID AND A.Created_TIME = X.last_time
LEFT JOIN( SELECT USER_ID,PRODUCT_ID, last_time
FROM TestingTable1 A
LEFT JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND ISNULL( B.PRODUCT_ID,0) =   A.ITEM_ID  
WHERE  B.last_time <> A.Created_TIME AND B.USER_ID IS NOT NULL) Y ON
A.BUYER_ID = Y.USER_ID AND A.ITEM_ID = Y.PRODUCT_ID

Answer 4:

这听起来像你正在寻找的是一个完整的外部联接。 我没有在您的文章您正在使用，所以我不能张贴的确切语法是什么类型的数据库中看到，但该链接可以指向你在正确的方向：

http://www.w3resource.com/sql/joins/perform-a-full-outer-join.php

具体看在底部的图。

文章来源: Multi Join in a single SQL query