跨表的 MySQL 嵌套集模型计数

Question

我有一个应用程序使用嵌套集模型类来组织我的数据，但是我正在尝试编写一个查询来计算每个组中的总人数。

表:person_to_group

 ----+------------+-----------
|ID  | Person_ID  | Group_ID  |
 ----+------------+-----------
| 1  | 3          | 1         |
| 2  | 3          | 2         |
| 3  | 5          | 2         |
| 4  | 7          | 3         |
 ----+------------+-----------

表:组

 ----------+--------------+--------------+-------------
|Group_ID  | Group_Name   | Group_Left   | Group_Right |
 ----------+--------------+--------------+-------------
| 1        | Root         | 1            | 6           |
| 2        | Node         | 2            | 5           |
| 3        | Sub Node     | 3            | 4           |
 ----------+--------------+--------------+-------------

我的查询将在一个 while 循环中运行，该循环列出所有组名。

我正在努力实现这样的结果:

根 - 成员 (3) <-------- 请注意，我希望将子组包含在计数中，并且我不希望对成员进行多次计数。

如有任何帮助，我们将不胜感激!

Answer 1

由于您在带有组名称的 while 循环中运行它，您可能可以获得组的左右值并将它们放入此查询中:

SELECT count(*) AS members FROM 
( SELECT DISTINCT ptg.person_ID FROM groups g 
JOIN person_to_group ptg
ON ptg.Group_ID=g.Group_ID
WHERE g.Group_Left  >= 1 
AND   g.Group_Right <= 6 ) m

这适用于给定的群体。如果您想在一次查询中获得一份完整的群组列表以及每个群组的成员数，您将不得不使用类似以下内容的方法:

SELECT m.Group_Name, count(*) AS members FROM
  ( SELECT g.Group_ID, g.Group_Name, ptg.Person_ID 
    FROM groups g 
    JOIN groups gsub 
      ON gsub.Group_Left >= g.Group_Left AND gsub.Group_Right <= g.Group_Right
    JOIN person_to_group ptg
      ON gsub.Group_ID = ptg.Group_ID
    GROUP BY g.Group_ID, ptg.Person_ID ) m
GROUP BY m.Group_ID

但是我认为第一个在 sql 之外使用循环的解决方案会更有效。