SQL 行乘法。 select 中同一个表中的数据彼此相邻

Question

我有四个表:文档、项目、文章和文档关系。在文档中，存在三种类型的文档，该问题仅涉及其中两种:订单和发票。发票是根据订单生成的。一张发票可以有多个订单，一个订单可以有多张发票。订单和发票可以有多个项目。表 DocumentRelations 包含订单和发票之间的关系，哪个发票是从哪个订单生成的。

Documents:
ID | Name
-- | -------- 
1  | O/2017/1
2  | I/2017/1
3  | O/2017/2
4  | I/2017/2
5  | O/2017/3
6  | O/2017/4
.. | ...
99 | X/2017/1

Ttems:
ID | ArticleID | DocumentID | Quantity
-- | --------- | ---------- | --------
1  | 1         | 1          | 12
2  | 1         | 2          | 3
3  | 2         | 3          | 41
4  | 2         | 4          | 41
5  | 1         | 4          | 59
6  | 1         | 5          | 59
7  | 3         | 6          | 7

Articles: 
ID | Name
-- | ----
1  | A
2  | B
3  | c

DocumentRelations:
OrderID | InvoiceID
------- | ---------
1       | 2
3       | 4
5       | 4
8       | 9
8       | 10
8       | 11

场景1:发票对应的订单不存在。订单对应的发票不存在。

场景 2:订单对应的发票所包含的每件商品的数量与发票的数量不同。发票对应的订单所包含的每件商品的数量与订单的数量不同。

我的问题开始了:我需要以易于比较的形式获取每个文档中包含数量的项目列表。因此，订单 ID、发票 ID、商品名称、订单数量、发票数量。

OrderID | InvoiceID | ItemNameInOrder | QuantityInOrder | QuantityInInvoice
------- | --------- | --------------- | --------------- | -----------------
1       | 2         | A               | 12              | 3
3       | 4         | B               | 41              | 41
5       | 4         | A               | 59              | 59
6       | NULL      | C               | 7               | NULL
NULL    | 7         | B               | NULL            | 11
8       | 9         | A               | 10              | 9
8       | 10        | A               | 10              | 9
8       | 11        | A               | 10              | 19

如果特定发票的订单不存在，则在名称和数量列中留空。表 DocumentRelations 包含三种类型的文档，因此不应出现第三种类型的文档。

我的第一个计划是进行两个选择，返回我只需要的列，每个选择用于文档类型。然后根据 DocumentRelations 中的 ID 将它们左连接。但这会使一切成倍增加。比预计结果高出约3倍。

   SELECT O.ID, I.ID, O.ArticleName, O.Quantity, I.Quantity
     FROM DocumentRelations R
LEFT JOIN (SELECT D.ID, D.NumberString, I.Quantity, A.Name
             FROM Documents D
             JOIN Items I 
               ON D.ID = I.DocumentID
             JOIN Articles A 
               ON I.ArticleID  = A.ID
            WHERE D.Name LIKE 'O/%') O 
       ON R.OrderID = O.ID
LEFT JOIN (SELECT D.ID, , I.Quantity, A.Name
             FROM Documents D
             JOIN Items I 
               ON D.ID = I.DocumentID
             JOIN Articles A 
               ON I.ArticleID  = A.ID
            WHERE D.Name LIKE 'I/%') I 
       ON R.InvoiceID = I.ID

第二个与第一个类似，但最后没有使用 DocumentRelations。创建两个选择，其中一个包含订单 ID、商品名称、商品数量和相应发票的 ID。第二个是相同的，但对于发票。根据相应文档的 ID 列，使用全外连接将它们连接起来，但结果是相同的。

SELECT O.ID, I.ID, O.ArticleName, O.Quantity, I.Quantity
  FROM (SELECT D.ID, D.NumberString, I.Quantity, A.Name, R.InvoiceID
          FROM Documents D
          JOIN Items I 
            ON D.ID = I.DocumentID
          JOIN Articles A 
            ON I.ArticleID  = A.ID
     LEFT JOIN DocumentRelations R 
            ON D.ID = R.OrderID
         WHERE D.Name LIKE 'O/%') O
FULL OUTER JOIN (
        SELECT D.ID, D.NumberString, I.Quantity, A.Name
          FROM Documents D
          JOIN Items I 
            ON D.ID = I.DocumentID
          JOIN Articles A 
            ON I.ArticleID  = A.ID
         WHERE D.Name LIKE 'I/%') I 
    ON O.InvoiceID = I.ID

Answer 1

我喜欢第二个 - 您只需将 ArticleID 添加到您的条件中除了缺少某些字符串的名称之外，您几乎会得到您想要的。我们假设您在一个文档中的同一篇文章没有两行或更多行:

SELECT O.ID, I.ID, O.ArticleName, O.Quantity, I.Quantity
  FROM (SELECT D.ID, D.NumberString, I.Quantity, A.Name, R.InvoiceID, D.ArticleID 
          FROM Documents D
          JOIN Items I 
            ON D.ID = I.DocumentID
          JOIN Articles A 
            ON I.ArticleID  = A.ID
     LEFT JOIN DocumentRelations R 
            ON D.ID = R.OrderID
         WHERE D.Name LIKE 'O/%') O
FULL OUTER JOIN (
        SELECT D.ID, D.NumberString, I.Quantity, A.Name, D.ArticleID
          FROM Documents D
          JOIN Items I 
            ON D.ID = I.DocumentID
          JOIN Articles A 
            ON I.ArticleID  = A.ID
         WHERE D.Name LIKE 'I/%') I 
            ON D.InvoiceID = I.ID
           AND D.ArticleID  = I.ArticleID