MySQL连接6表中的完全连接

Question

我在MySql有一个关于完全加入的问题。我知道另一种选择是联合，但我有办法把它们结合起来。

我想这已经足够在4张桌子上得到答案了，因为第5和第6是相同的，在前4。

表:账单、服务、BS、项目、BI、买方

B将更多的服务连接到Bill，BI将更多的项目连接到Bill。买方与汇票的关系为1:1。

表格示例：

Bill:
----------------------
id | number | Buyer_id
1  | 12014  |    3
2  | 22014  |    2
3  | 32014  |    5

Services:
----------------------
id | cost
1  |   2
2  |   7
3  |   1
4  |  12

BS:
----------------------
id | Bill_id | Services_id
1  |    1    |     3
2  |    1    |     4
3  |    2    |     2
4  |    3    |     1
5  |    3    |     2
6  |    3    |     3
7  |    3    |     4

Item:
----------------------
id | cost
1  |  34
2  |  77
3  |   2
4  |  15
5  |  13

BI:
----------------------
id | Bill_id | Items_id
1  |    1    |    5
2  |    2    |    3
3  |    3    |    2

Buyer:
----------------------
id | name
1  | John
2  | Mary
3  | Dave
4  | Carl
5  | Jack

到目前为止，我得到的最接近的是我使用了以下SQL：

SELECT *
FROM Bill b
LEFT JOIN BI ON BI.Bill_id = b.id
LEFT JOIN BS ON BS.Bill_id = b.id
LEFT JOIN Item i ON i.id = BI.Item_id
LEFT JOIN Services s ON s.id = BS.Services_id
LEFT JOIN Buyer ON Buyer.id = b.Buyer_id
WHERE b.number = '12014'

结果给我两个服务和一个重复的项目，但我想要一个项目和一个空项目(因为只有一个项目是附加到该帐单。

结果我得到(只是it，因为它更短)：

b.id | s.id | BS.id | i.id | BI.id | Buyer.id
  1  |   3  |   1   |  1   |   5   |   3
  1  |   4  |   2   |  1   |   5   |   3

表中的预期结果：

b.id | s.id | BS.id | i.id | BI.id | Buyer.id
  1  |   3  |   1   |  1   |   5   |   3
  1  |   4  |   2   | NULL | NULL  |   3 (or NULL, doesn't really matter)

我也尝试与其他人，但我得到的行甚至超过2(请注意，两行是预期的，如果Bill.number=32014，然后4行)。

谢谢!

Answer 1

你走错路了。

对关系数据库的查询的结果是一种关系--您可以将其视为带有行和列的矩形矩阵。每一行代表某种东西(至少应该如此)，每一列代表一个属性，每个单元格代表事物的属性值。

          |  [attr 1]   |  [attr 2]
-----------+---------------+-------------------------
[thing 1] |   value     |   some value
[thing 2] |   value     |   other value
[thing 3] |   a value   |   yeah, a value

下面是你们想要生产的东西：

          |  [attr 1]   |  [attr 2]      |                   |  [other attr 3]
-----------+---------------+------------------+---------------------+-------------------
[thing 1] |   value     |   some value   |  [other thing 1]  |   a value
[thing 2] |   value     |   other value  |                  

看见?试图用一个查询返回两个关系。不再是长方形了，是吧？项目和服务在这里是独立的，但是您试图将它们放到一个行中。不要走这条路，这里有三个查询：

-- Get bill/buyer details (1 row)
SELECT b.id, Buyer.id
FROM Bill b
LEFT JOIN Buyer ON Buyer.id = b.Buyer_id
WHERE b.number = '12014';

-- Get billed items (1 row per item)
SELECT BI.id, i.id
FROM Bill b
JOIN BI ON BI.Bill_id = b.id
JOIN Item i ON i.id = BI.Item_id
WHERE b.number = '12014';

-- Get billed services (1 row per service)
SELECT BS.id, s.id
FROM Bill b
JOIN BS ON BS.Bill_id = b.id
JOIN Services s ON s.id = BS.Services_id
WHERE b.number = '12014';

注意，项和服务查询不使用左联接。如果账单上没有项目/服务，您希望返回0行。

然后在您的应用程序中逐一处理它们的结果。

编辑：

有时，两个(或更多)实体共享一些共同的特性，例如，在应用程序中，您可以将服务和项视为账单行。在这种情况下，这可以有效地在一个查询中检索所有这些数据，但只能使用union：

-- Get bill lines (items and services)
SELECT BI.id AS bill_item_id, i.id AS item_id, NULL as bill_service_id, NULL as service_id
FROM Bill b
JOIN BI ON BI.Bill_id = b.id
JOIN Item i ON i.id = BI.Item_id
WHERE b.number = '12014';
UNION ALL
SELECT NULL AS bill_item_id, NULL AS item_id, BS.id as bill_service_id, s.id as service_id
FROM Bill b
JOIN BS ON BS.Bill_id = b.id
JOIN Services s ON s.id = BS.Services_id
WHERE b.number = '12014';

它将返回一个与您最初预期的类似的结果：

BI.id |  i.id | BS.id | s.id
  5   |   1   | NULL | NULL
NULL  | NULL  |  1   |  3
NULL  | NULL  |  2   |  4

请注意：

• 每个项目和服务都是由记录表示的单个票据行。不要人为地“压缩”跨行或列的数据
• 在您的模式中，情况并非如此，但通常也有一些共享属性，如行id、订购数量或支付金额。