sql - SQL:从两个表生成矩阵具有多对多关系

我有三张桌子：droit，role和role。我在droit和role之间有多对多的关系，role_droit作为关联表。请在下面的图表中找到。我将返回这样一个矩阵，下面的图表命名为：Result 2如果droit和role之间有一个交集，则我们将1设为0。我尝试了此查询，但在图表结果1中得到了结果：

SELECT matrix_view.droit_id,
    case when matrix_view.aid = 1 then haspair end as  "role A" ,
    case when matrix_view.aid = 2 then haspair end as  "role B" ,
    case when matrix_view.aid = 3 then haspair end as  "role C" ,
    case when matrix_view.aid = 4 then haspair end as  "role D"

        from (SELECT allRD.aid as aid, allRD.droit_id, max(case when RD.role_id is not null then 1 else 0 end) as HasPair
        from (select distinct a.role_id as aid, b.droit_id as droit_id
        from role a cross join droit b ) as allRD left outer join role_Droit RD
        on allRD.aid = RD.role_id and allRD.droit_id = RD.droit_id
        group by  allRD.droit_id,  allRD.aid
        order by  allRD.aid) AS matrix_view

我用Postgres做SGBD。请帮助我得到结果2！!!
请检查所附图片以显示表格和结果
enter image description here

最佳答案

试试下面的查询。
我只将您的查询扩展了group by列。并与sum合并。

select A.droit_id, sum(A."role A") as "role A", sum(A."role B") as "role B", sum(A."role C") as "role C", sum(A."role D") as "role D"
from (
    SELECT matrix_view.droit_id,
        case when matrix_view.aid = 1 then haspair end as  "role A" ,
        case when matrix_view.aid = 2 then haspair end as  "role B" ,
        case when matrix_view.aid = 3 then haspair end as  "role C" ,
        case when matrix_view.aid = 4 then haspair end as  "role D"

    from (
        SELECT allRD.aid as aid, allRD.droit_id, max(case when RD.role_id is not null then 1 else 0 end) as HasPair
            from (select distinct a.role_id as aid, b.droit_id as droit_id
            from role a cross join droit b ) as allRD left outer join role_Droit RD
            on allRD.aid = RD.role_id and allRD.droit_id = RD.droit_id
            group by  allRD.droit_id,  allRD.aid
            order by  allRD.aid
    ) AS matrix_view
) A
group by A.droit_id
order by A.droit_id

或者这个：

SELECT matrix_view.droit_id,
    sum(case when matrix_view.aid = 1 then haspair end) as  "role A" ,
    sum(case when matrix_view.aid = 2 then haspair end) as  "role B" ,
    sum(case when matrix_view.aid = 3 then haspair end) as  "role C" ,
    sum(case when matrix_view.aid = 4 then haspair end) as  "role D"

from (
        SELECT allRD.aid as aid, allRD.droit_id, max(case when RD.role_id is not null then 1 else 0 end) as HasPair
        from (
                select distinct a.role_id as aid, b.droit_id as droit_id
                from role a cross join droit b
             ) as allRD
        left outer join role_Droit RD
        on allRD.aid = RD.role_id and allRD.droit_id = RD.droit_id
        group by  allRD.droit_id,  allRD.aid
        order by  allRD.aid
    ) AS matrix_view
group by matrix_view.droit_id
order by matrix_view.droit_id

关于sql - SQL:从两个表生成矩阵具有多对多关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48371973/