奇怪的工会类型

Question

很抱歉这个奇怪的标题，我对SQL没有太多经验。我正在使用 2 个表：表 COMPONENTS ，其中列出了一些组件，表 OPERATIONS 是应用于组件的操作列表。

表 COMPONENTS 如下所示：

+-------+--------------+-------------------+-------+
| Level | Main Article | Secondary Article | Brand |
+-------+--------------+-------------------+-------+
|     1 | Article A    | Article 1         | Foo   |
|     1 | Article B    | Article 1         | Bar   |
+-------+--------------+-------------------+-------+

它有更多的列，但这就是想法。基本上，它列出了所有库存物品以及它们是由什么物品制成的。

OPERATIONS表包含次要文章必须经历的所有操作才能合并到主文章中：

+--------------------+-----------+------+
| Secondary Article  | Operation | Cost |
+--------------------+-----------+------+
| Article 1          | Cutting   | X    |
| Article 1          | Knitting  | Y    |
| Article 1          | Bleaching | Z    |
+--------------------+-----------+------+

将这两个表合并为一个具有此结构的最佳方法是什么？

+-------+--------------+-------------------+-----------+------+-------+
| Level | Main Article | Secondary Article | Operation | Cost | Brand |
+-------+--------------+-------------------+-----------+------+-------+
|     1 | Article A    | Article 1         |           |      | Foo   |
|     1 | Article A    | Article 1         | Cutting   | X    | Foo   |
|     1 | Article A    | Article 1         | Knitting  | Y    | Foo   |
|     1 | Article A    | Article 1         | Bleaching | Z    | Foo   |
|     1 | Article B    | Article 1         |           |      | Bar   |
|     1 | Article B    | Article 1         | Cutting   | X    | Bar   |
|     1 | Article B    | Article 1         | Knitting  | Y    | Bar   |
|     1 | Article B    | Article 1         | Bleaching | Z    | Bar   |
+-------+--------------+-------------------+-----------+------+-------+

我试图尽可能地简化问题。我应该如何继续管理？我尝试过加入和工会，但它不起作用。

在我看来，使用允许循环的编程语言来做到这一点非常容易，但我在这里完全迷失了方向。

Answer 1

与其使用UNION，我会做这样的事情：

SELECT C.Level,
       C.MainArticle,
       C.SecondaryArticle,
       O.Operation,
       O.Cost,
       C.Brand
FROM COMPONENTS C
     CROSS APPLY (VALUES(0),(1)) V(Header)
     LEFT JOIN OPERATIONS O ON V.Header = 1
                           AND C.SecondaryArticle = O.SecondaryArticle
ORDER BY C.MainArticle,
         V.Header,
         O.Cost;

数据库<>小提琴

Answer 2

你的join/union直觉是对的，你可以做这样的事情：

select
    Level,
    `Main Article`,
    `Secondary Article`,
    '' as Operation,
    '' as Cost,
    Brand
from
    COMPONENTS
union select
    COMPONENTS.Level,
    COMPONENTS.`Main Article`,
    COMPONENTS.`Secondary Article`,
    OPERATIONS.Operation,
    OPERATIONS.Cost,
    COMPONENTS.Brand
from
    COMPONENTS
    left join OPERATIONS on COMPONENTS.`Secondary Article` = OPERATIONS.`Secondary Article`

Answer 3

这

似乎是一个union all，join：

select c.Level, c.MainArticle, c.SecondaryArticle, 
       NULL as operation, NULL as cost, c.brand
from components c
union all
select c.level, c.MainArticle, c.SecondaryArticle,
       o.operation, o.cost, c.brand
from components c join
     operations o
     on c.SecondaryArticle = o.SecondaryArticle;

如果您关心结果集中的顺序，请使用：

order by MainArticle, SecondaryArticle

以及您可能想要的任何其他密钥。

您也可以在join之前执行union all，尽管这可能会影响性能：

select c.level, c.MainArticle, c.SecondaryArticle,
       o.operation, o.cost, c.brand
from components c join
     ((select o.SecondaryArticle, o.operation, o.cost
       from operations o
      ) union all
      (select c.SecondaryArticle, NULL, NULL
       from components c
      )
     ) o
     on c.SecondaryArticle = o.SecondaryArticle;