sql - 如何通过其他 3 个表的每种可能组合对表中的一组行进行分组？

Question

这是表结构

Table A (id, name)
Table B (id, A-id)
Table C (id, quantity, B-id, D-id)
Table D (id, E-id, F-id)
Table E (id, name)
Table F (id, name)

我想得到这样的结果

A.name | E.name | F.name | SUM(C.quantity) 
-------------------------------------------
foo    | aaa    | zzz    | 50
-------------------------------------------
foo    | aaa    | xxx    | 0
-------------------------------------------
foo    | bbb    | www    | 10
-------------------------------------------
bar    | aaa    | zzz    | 12
-------------------------------------------
bar    | aaa    | xxx    | 1
-------------------------------------------
bar    | bbb    | www    | 30
-------------------------------------------

我必须显示 A、E 和 F 中的所有条目，即使在 C 中没有与它们相关的条目。

所以基本上我必须通过 A、E 和 F 的所有可能组合对 C 中的所有数据进行分组code> 并在 C 中没有可用数据时显示 0。

我该怎么做？

Answer 1

利用CROSS JOIN, LEFT [OUTER] JOIN和 COALESCE :

如果您想要一个CROSS JOIN，这很不寻常并且可能会产生很多行，它可能看起来像这样。更新后更加激进:b 和 d 现在也是可选的。

SELECT a.name AS a_name, e.name AS e_name, f.name AS f_name
      ,COALESCE(sum(c.quantity), 0) As sum_quantity
FROM   a
CROSS  JOIN e
CROSS  JOIN f
LEFT   JOIN b ON b.a_id = a.id
LEFT   JOIN d ON d.e_id = e.id
             AND d.f_id = f.id
LEFT   JOIN c ON c.b_id = b.id
             AND c.d_id = d.id
GROUP  BY 1,2,3;

由于缺乏规范，只有当 b_id 和 d_id 匹配时，我才加入 c。

评论补充Q

In your last answer you put a parenthesis after cross join, what doesthat do?

我引用 the manual here :

Use parentheses if necessary to determine the order of nesting. Inthe absence of parentheses, JOINs nest left-to-right. In any case JOINbinds more tightly than the commas separating FROM items.