具有Count的内部联接，多个表

Question

我在MS Access获取多个表的正确计数时遇到问题。我根本不是SQL请求方面的专家。

我有一组表格，其结构如下：

• 表1:UID、名称和其他信息
• 表2:UID、FK_UID_Table1、名称和其他信息
• 表3:UID、FK_UID_Table2、名称和其他信息
• 表4:UID、FK_UID_Table3、名称和其他信息

每个FK引用都是1-N，其中N>=0。因此，表2中的项目可能在表3 FK.中没有引用

我试图实现的是在一个查询中获得每个项目的计数。

例如，如果我在表1中选择一个项目，我想得到：

Table1.Name|Count(Table2.UID)|Count(Table3.UID)|Count(Table4.UID)

我成功地用一个INNER JOIN获得了确切的计数，如下所示：

SELECT g.UID, Table1.Name, COUNT(s.UID) AS CountTable2
FROM Table1 AS g INNER JOIN Table2 AS s ON s.FK_Table1 = g.UID
GROUP BY g.UID, g.Name

例如，对于Table1中的一个项目，它在Table2中获得2个引用，在Table3中获得3个引用，因此我获得2个，这是正确的。

当我尝试添加另一层计数时，我做了如下操作：

SELECT g.UID, g.Name, COUNT(s.UID) AS CountTable2, COUNT(p.UID) AS CountTable3
FROM (Table1 as g
INNER JOIN Table2 AS s ON s.FK_Table1 = g.UID)
INNER JOIN Table3 AS p ON p.FK_Table2 = s.UID
GROUP BY g.UID, g.Name, CountTable2, CountTable3

在我前面的例子中，结果不是CountTable2得到2，CountTable3得到3，而是得到3和3。

我读到Access的预期行为，但我不知道如何让它发挥作用。

这个问题帮助了我：内部连接，三张表上有计数

任何帮助都将不胜感激。

Answer 1

加入前的聚合：

SELECT g.UID, g.Name, s.CountTable2, p.CountTable3
FROM (Table1 g LEFT JOIN
      (SELECT s.FK_Table1, COUNT(*) as CountTable2
       FROM Table2
       GROUP BY s.FK_Table1
      ) AS s
      ON s.FK_Table1 = g.UID
     ) LEFT JOIN
     (SELECT s.FK_Table1, COUNT(*) as CountTable3
      FROM Table3 as p JOIN
           Table2 as s
           ON p.FK_Table2 = s.UID
      GROUP BY s.FK_Table1
     ) as p
     ON p.FK_Table1 = g.UID;

Answer 2

由于Gordon Linoff的回答没有成功，我尝试了另一种方法使用LEFT OUTER JOIN。

感谢这篇帖子：如何获得计数为零的组

我设法用void关系得到了一个正确的结果(例如，一个项目Table2在Table3中没有引用(，但现在当我连接两个以上的表时，我得到了不正确的结果。

我想这是我请求的问题。。。

目前：

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID
GROUP BY Table1.UID, Table1.Name

给了我一个正确的结果(只有两个表相关(，但是：

SELECT Table1.UID, Table1.Name
COUNT(Table2.UID) AS CountTable2, COUNT(Table3.UID) AS CountTable3
FROM (Table1
LEFT OUTER JOIN Table2 ON Table2.FK_Table1 = Table1.UID)
LEFT OUTER JOIN Table3 ON Table3.FK_Table2 = Table2.UID
GROUP BY Table1.UID, Table1.Name

给了我一个不正确的CountTable2结果，它似乎比预期的要多。CountTable3正确。

编辑：

根据我的研究和Gordon Linoff在加入之前对聚合的暗示，我终于想好了如何让它发挥作用。

我从计算上表中最深的表开始，然后加入，依此类推。在每一步中，我都会选择要保留的基本信息：UID、FK_AboveTable、Count、Sums from depth table。

最终代码：

SELECT Table1.UID, Table1.Name, COUNT(Table2.UID) AS TotalTable2, SUM(CountTable3) AS TotalTable3, SUM(CountTable4_2) AS TotalTable4
FROM Table1 LEFT OUTER JOIN (
    SELECT Table2.UID, Table2.FK_Table1, COUNT(Table3.UID) AS CountTable3, 
    SUM(CountTable4) AS CountTable4_2
    FROM Table2 LEFT OUTER JOIN (
        SELECT Table3.UID, Table3.FK_Table1, COUNT(Table4.UID) AS CountTable4
        FROM Port LEFT OUTER JOIN
            Table4 ON Table4.FK_Table3 = Table3.UID
            GROUP BY Table3.UID, Table3.FK_Table2
        ) Table3 ON Table3.FK_Table2 = Table2.UID
        GROUP BY Table2.UID, Table2.FK_Table1
    ) Table2 ON Table2.FK_Table1= Table1.UID
GROUP BY Table1.UID, Table1.Name ORDER BY Table1.Name DESC

请注意，显示的最深表中的void计数为void且没有0(例如，如果表1中有一个项，而表2中没有相关项，则表2的计数为0，表3和表4的计数为void(。

我认为这可能会升级，但目前它解决了我的问题，并允许我添加所需的表。