mysql - 计算组中用户的成员资格

Question

我有一个协会的树结构，它分为部门、分割等。在每个级别上，用户可能拥有某些角色的成员资格。

我想计算表中定义的每个“结构类型”(关联、分部、分割)的成员数

表结构如下:

table intern_structures
Contains the hierarchy (nested set, but that does not matter here)
id | intern_structure_type_id |       name       | parent_id | lft |  rgt
 1            1                   My Company USA       0        1      6
 2            2                      Texas             1        2      5
 3            3                      El Paso           2        3      4        

table intern_structure_types 
Contains Description to the types like "association", "division", "subdivision"
id |     name
 1     Association
 2     Division
 3     Subdivision


table memberships
Contains the memberships 
id | user_id | intern_structure_id | role_id
 1      1              1                1
 2      1              2                2
 3      2              3                1
 3      2              3                3
 ....      

table roles
Contains role descriptions
id |  name 
 1    Admin
 2    Moderator
 3    Clerk

我想要一个像这样的分组列表:

structure_type_name     | role_name      | count of memberships
    Association            Admin                   1
    Association            Moderator              10
    Association            Clerk                   0   << !! I miss the zero rows!
    Division               Admin                   7
    Divison                Moderator              43
    Division               Clerk                  31
    Subdivision            Admin                 234
    Subdivision            Moderator             942
    Subdivision            Clerk                 456

到目前为止我实现的是这个查询:

SELECT 
    is_types.name,
    roles.name,
    COUNT(memberships.id)
FROM
    roles,
    intern_structure_types AS is_types
        LEFT JOIN intern_structures AS is_elements ON is_elements.intern_structure_type_id = is_types.id    
        LEFT JOIN memberships ON memberships.intern_structure_id = is_elements.id
WHERE
    roles.id = memberships.role_id 
GROUP BY
    is_types.id, roles.id

它工作正常除了它没有列出所有角色，因为有些角色还没有任何成员资格，但我希望它们也被列出，成员数为 0。

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

Answer 1

我假设您在 OP 中显示的计数是人为设计的。要获得所需的结果，您应该创建一个正在使用的类型和角色的派生表，然后将整个查询左连接到角色和类型的交叉连接。

Select  is_types.name
    , roles.name
    , Count(Z.is_type_name) 
From roles
    Cross Join intern_structure_types As is_types
    Left Join   (
                Select is_types.name As is_type_name
                    , roles.name As role_name
                From intern_structures As is_elements 
                    Join intern_structure_types As is_types
                        On is_types.id = is_elements.intern_structure_type_id
                    Join memberships
                        On memberships.intern_structure_id = is_elements.id
                    Join roles
                        On roles.id = memberships.role_id
                ) As Z
        On Z.is_type_name = is_types.name
            And Z.role_name = roles.name
Group By is_types.name, roles.name