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

我有一个树形结构的一个协会，它被划分为部门，细分等，在每一个级别上，用户可能有某些角色的成员资格。
我要计算表中定义的每个“结构类型”（关联、分区、子分区）的成员资格
表结构如下：

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作为成员资格计数。
我非常感谢你的帮助！

最佳答案

我猜你在手术中所显示的数字是人为的。为了得到您想要的结果，您应该创建一个正在使用的类型和角色的派生表，然后将整个查询左键连接到角色和类型的交叉连接。

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