select T1.deptId,T2.roleId,NVL(T3.S,0) FROM (select distinct deptId from dept_employee)T1, (select distinct roleId from role_employee) T2, (select A.deptId,B.roleId,count(1) S from dept_employee A,role_employee B WHERE A.empolyeeId=B.empolyeeId GROUP BY A.deptId,B.roleId) T3 where T1.deptId(+)=T3.deptId AND T2.roleId(+)=T3.roleId
with T AS (select T1.deptId,T2.roleId FROM (select distinct deptId from dept_employee)T1, (select distinct roleId from role_employee) T2 ),T3 AS (select A.deptId,B.roleId,count(1) S from dept_employee A,role_employee B WHERE A.empolyeeId=B.empolyeeId GROUP BY A.deptId,B.roleId) select T.deptId,T.roleId,NVL(T3.S,0) FROM T left join T3 ON T.deptId=T3.deptId AND T.roleId=T3.roleId
(select distinct deptId from dept_employee)T1,
(select distinct roleId from role_employee) T2,
(select A.deptId,B.roleId,count(1) S
from dept_employee A,role_employee B
WHERE A.empolyeeId=B.empolyeeId
GROUP BY A.deptId,B.roleId) T3
where T1.deptId(+)=T3.deptId
AND T2.roleId(+)=T3.roleId
这个也不行,因为以T3为基表,外连接T1和T2,但是T3查询出来的结果集是只有角色和部门下有人的情况,所以角色和部门下没有人的角色和部门就不会被查询出来了
(select distinct deptId from dept_employee)T1,
(select distinct roleId from role_employee) T2
),T3 AS (select A.deptId,B.roleId,count(1) S
from dept_employee A,role_employee B
WHERE A.empolyeeId=B.empolyeeId
GROUP BY A.deptId,B.roleId)
select T.deptId,T.roleId,NVL(T3.S,0) FROM
T left join T3
ON T.deptId=T3.deptId
AND T.roleId=T3.roleId