MySQL 跨表嵌套集模型计数

Question

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

表：person_to_group

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

表：groups

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

我的查询将在列出所有组名称的 while 循环内运行。

我正在尝试实现这样的结果：

Root - Members (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 之外使用循环的解决方案会更有效。