LEFT OUTER JOIN 和 NOT EXISTS 查询

Question

我在处理一项复杂的联接操作时遇到了困难，需要将两个相同的表进行联接，并确保每次联接都只与特定记录（最近的记录）进行。

SELECT c.collection_id      AS collections, 
       s.strat_id           AS StrategyId, 
       s.strat_version      AS StrategyVersion, 
       ssb.side_ordering    AS SSB_SO, 
       ssb2.side_ordering   AS SSB2_SO, 
       Invoice1.invoice_id  AS Inv1ID, 
       Invoice2.invoice_id  AS Inv2ID, 
       Invoice1.printeddate AS Inv1PrintedDate, 
       Invoice2.printeddate AS Inv2PrintedDate 
  FROM dbo.collections AS c 
       INNER JOIN dbo.strategies AS s 
               ON c.collection_id = s.collection_id 
       INNER JOIN dbo.side_strat_brkrgs AS ssb 
               ON s.collection_id = ssb.collection_id 
                  AND s.strat_id = ssb.strat_id 
                  AND s.strat_version = ssb.strat_version 
       INNER JOIN dbo.strat_sides AS ss 
               ON ss.strat_side_id = ssb.strat_side_id 
       LEFT OUTER JOIN dbo.side_strat_brkrgs AS ssb2 
                    ON ssb2.collection_id = ssb.collection_id 
                       AND ssb2.strat_id = ssb.strat_id 
                       AND ssb2.strat_version = ssb.strat_version 
                       AND ssb2.side_ordering <> ssb.side_ordering 
       INNER JOIN dbo.strat_sides AS ss2 
               ON ss2.strat_side_id = ssb2.strat_side_id 
       LEFT OUTER JOIN dbo.newinvoiceitem AS InvoiceItem1 
                    ON ssb.collection_id = InvoiceItem1.collection_id 
                       AND ssb.side_ordering = InvoiceItem1.side_ordering 
                       AND s.strat_id = InvoiceItem1.strat_id 
       LEFT OUTER JOIN dbo.newinvoice AS Invoice1 
                    ON Invoice1.invoice_id = InvoiceItem1.invoice_id 
       LEFT OUTER JOIN dbo.newinvoiceitem AS InvoiceItem2 
                    ON ssb2.collection_id = InvoiceItem2.collection_id 
                       AND ssb2.side_ordering = InvoiceItem2.side_ordering 
                       AND s.strat_id = InvoiceItem2.strat_id 
       LEFT OUTER JOIN dbo.newinvoice AS Invoice2 
                    ON Invoice2.invoice_id = InvoiceItem2.invoice_id 
 WHERE NOT EXISTS (SELECT 1 
                     FROM dbo.newinvoiceitem tempInvoiceItem1 
                          INNER JOIN dbo.newinvoice AS tempInvoice1 
                                  ON tempInvoice1.invoice_id = 
                                     tempInvoiceItem1.invoice_id 
                    WHERE tempInvoiceItem1.collection_id = ssb.collection_id 
                          AND ssb.side_ordering = tempInvoiceItem1.side_ordering 
                          AND s.strat_id = tempInvoiceItem1.strat_id 
                          AND s.strat_version = tempInvoiceItem1.strat_version 
                          AND tempInvoice1.printeddate > Invoice1.printeddate) 
       AND NOT EXISTS (SELECT 1 
                         FROM dbo.newinvoiceitem tempInvoiceItem2 
                              INNER JOIN dbo.newinvoice AS tempInvoice2 
                                      ON tempInvoice2.invoice_id = 
                                         tempInvoiceItem2.invoice_id 
                        WHERE 
               tempInvoiceItem2.collection_id = ssb2.collection_id 
               AND ssb2.side_ordering = tempInvoiceItem2.side_ordering 
               AND s.strat_id = tempInvoiceItem2.strat_id 
               AND s.strat_version = tempInvoiceItem2.strat_version 
               AND tempInvoice2.printeddate > Invoice2.printeddate) 
       AND c.collection_id = 16447 

我得到了以下结果：

16447   1   3   1   0   3785    3183    2010-05-06 17:52:00 2010-05-06 17:52:00 
16447   1   3   1   0   3785    4033    2010-05-06 17:52:00 2010-05-10 16:32:00 
16447   1   3   1   0   4137    3183    2010-05-20 17:08:00 2010-05-06 17:52:00 
16447   1   3   1   0   4137    4033    2010-05-20 17:08:00 2010-05-10 16:32:00

我只希望得到最后一行，但我错在哪里呢？

正如您所见，我不能使用MAX()来检索最高记录，因为我需要通过连接查找另一个属性，并且我被迫使用NOT EXIST。

- Edmondo

---

如果这是针对大数据集的话，为了优化和可扩展性，我会考虑避免使用左连接，并将其拆分成更小、更易管理的块 :) - LukeHennerley

你能调整一下缩进吗？现在看起来很难读。 - Michael Berkowski

你的查询有点复杂，让我有些难以理解。通常情况下，当我们想要获取最新的记录时，会使用MAX()函数来选择最高的发票号码或最新的日期。 - grahamj42

你的第一个查询无法解析...请使用这个工具来格式化你的查询... - Iswanto San

我该如何更好地格式化它？ - Edmondo

@Edmondo1984 在逻辑上换行，而不是在编辑器自动换行的地方 - 在FROM下缩进表格和连接， 在SELECT下缩进列，在连接的ON条件中使其与所属的连接有视觉关联。 - Michael Berkowski

Answer 1

尝试使用这个谓词。

 WHERE EXISTS (SELECT 1 
               FROM dbo.newinvoiceitem tempInvoiceItem1 
                 INNER JOIN dbo.newinvoice AS tempInvoice1 
                   ON tempInvoice1.invoice_id = tempInvoiceItem1.invoice_id 
               WHERE tempInvoiceItem1.collection_id = ssb.collection_id 
                 AND ssb.side_ordering = tempInvoiceItem1.side_ordering 
                 AND s.strat_id = tempInvoiceItem1.strat_id 
               HAVING MAX(tempInvoice1.printeddate) = Invoice1.printeddate
                 OR Invoice1.printeddate IS NULL
               )                          
       AND EXISTS (SELECT 1 
                   FROM dbo.newinvoiceitem tempInvoiceItem2 
                     INNER JOIN dbo.newinvoice AS tempInvoice2 
                       ON tempInvoice2.invoice_id = tempInvoiceItem2.invoice_id 
                   WHERE tempInvoiceItem2.collection_id = ssb2.collection_id 
                     AND ssb2.side_ordering = tempInvoiceItem2.side_ordering 
                     AND s.strat_id = tempInvoiceItem2.strat_id 
                   HAVING MAX(tempInvoice2.printeddate) = Invoice2.printeddate
                     OR Invoice2.printeddate IS NULL
                   ) 
       AND c.collection_id = 16447 

或者

 WHERE EXISTS (SELECT 1 
               FROM dbo.newinvoiceitem tempInvoiceItem1 
                 INNER JOIN dbo.newinvoice AS tempInvoice1 
                   ON tempInvoice1.invoice_id = tempInvoiceItem1.invoice_id 
               WHERE tempInvoiceItem1.collection_id = ssb.collection_id 
                 AND ssb.side_ordering = tempInvoiceItem1.side_ordering 
                 AND s.strat_id = tempInvoiceItem1.strat_id 
               HAVING MAX(tempInvoice1.printeddate) = Invoice1.printeddate                     
               )                          
       AND EXISTS (SELECT 1 
                   FROM dbo.newinvoiceitem tempInvoiceItem2 
                     INNER JOIN dbo.newinvoice AS tempInvoice2 
                       ON tempInvoice2.invoice_id = tempInvoiceItem2.invoice_id 
                   WHERE tempInvoiceItem2.collection_id = ssb2.collection_id 
                     AND ssb2.side_ordering = tempInvoiceItem2.side_ordering 
                     AND s.strat_id = tempInvoiceItem2.strat_id 
                   HAVING MAX(tempInvoice2.printeddate) = Invoice2.printeddate
                   ) 
       AND c.collection_id = 16447 OR Invoice1.printeddate IS NULL
         OR Invoice2.printeddate IS NULL

- Aleksandr Fedorenko

---

这对我返回了一个空集合：( - Edmondo

这个39分钟前编辑的最新版本可行。你能解释一下原理吗？ - Edmondo

我认为EXISTS()运算符中的条件 s.strat_version = tempInvoiceItem1.strat_version 过于冗长，因为为了进行行数据比较，我们需要相同的数据集。 - Aleksandr Fedorenko

回到主题，这将左外连接转换为内连接... 我也想要NULL。 - Edmondo

没问题;) 只需添加条件Invoice1.printeddate IS NULL/Invoice2.printeddate IS NULL。回答已更新。 - Aleksandr Fedorenko