sql - 检查 PostgreSQL 中组查询中的单个行

Question

WITH 
Emp(DName, EName, Role) AS (
     VALUES ('D1','E11', 'Role1'), 
            ('D1','E11', 'Role2'),
            ('D1','E12', 'Role1'), 
            ('D2','E12', 'Role1')
) 

对于每个部门，获取每个角色的员 worker 数。
输出应该是:

#_of_Role1_Employees; #_of_Role2_Employees
2; 1
1; 0

Answer 1

WITH 
Emp(DName, EName, Role) AS (
     VALUES ('D1','E11', 'Role1'), 
            ('D1','E11', 'Role2'),
            ('D1','E12', 'Role1'), 
            ('D2','E12', 'Role1')
)
select
    dname "Dname",
    count(role = 'Role1' or null) "Role1",
    count(role = 'Role2' or null) "Role2"
from emp
group by dname
;
 Dname | Role1 | Role2 
-------+-------+-------
 D1    |     2 |     1
 D2    |     1 |     0

更新

这应该符合不同的要求:

WITH
Emp(DName, EName, Role) AS (
     VALUES ('D1','E11', 'Role1'),
            ('D1','E11', 'Role2'),
            ('D1','E12', 'Role1'),
            ('D2','E12', 'Role1')
)
select
    dname "Dname",
    count(role = 'Role1' or null) "Role1",
    count(role = 'Role2' or null) "Role2"
from (
    select distinct *
    from emp
) s
group by dname
;
 Dname | Role1 | Role2 
-------+-------+-------
 D1    |     2 |     1
 D2    |     1 |     0