17,377
社区成员
发帖
与我相关
我的任务
分享
with dept as
(
select '部门A' dname, 100 as empno from dual
union all
select '部门A' dname, 101 as empno from dual
union all
select '部门A' dname, 102 as empno from dual
union all
select '部门A' dname, 103 as empno from dual
union all
select '部门A' dname, 104 as empno from dual
union all
select '部门A' dname, 105 as empno from dual
union all
select '部门A' dname, 106 as empno from dual
union all
select '部门A' dname, 107 as empno from dual
union all
select '部门B' dname, 200 as empno from dual
union all
select '部门B' dname, 201 as empno from dual
union all
select '部门B' dname, 202 as empno from dual
union all
select '部门B' dname, 203 as empno from dual
union all
select '部门B' dname, 204 as empno from dual
union all
select '部门B' dname, 205 as empno from dual
union all
select '部门B' dname, 206 as empno from dual
union all
select '部门B' dname, 207 as empno from dual
),
emp as
(
select 100 as empno, 1 as lvl from dual
union all
select 101 as empno, 1 as lvl from dual
union all
select 102 as empno, 2 as lvl from dual
union all
select 103 as empno, 2 as lvl from dual
union all
select 104 as empno, 2 as lvl from dual
union all
select 105 as empno, 3 as lvl from dual
union all
select 106 as empno, 3 as lvl from dual
union all
select 107 as empno, 3 as lvl from dual
union all
select 200 as empno, 4 as lvl from dual
union all
select 201 as empno, 2 as lvl from dual
union all
select 202 as empno, 2 as lvl from dual
union all
select 203 as empno, 2 as lvl from dual
union all
select 204 as empno, 2 as lvl from dual
union all
select 205 as empno, 3 as lvl from dual
union all
select 206 as empno, 3 as lvl from dual
union all
select 207 as empno, 3 as lvl from dual
)
select lvl,
sum(case when dname = '部门A' then 1 else 0 end) as "部门A",
sum(case when dname = '部门B' then 1 else 0 end) as "部门B"
from dept a right join emp b
on a.empno = b.empno
group by lvl
order by lvl;
LVL 部门A 部门B
---------- ---------- ----------
1 2 0
2 3 4
3 3 3
4 0 1
with dept as
(
select '部门A' dname, 100 as empno from dual
union all
select '部门A' dname, 101 as empno from dual
union all
select '部门A' dname, 102 as empno from dual
union all
select '部门A' dname, 103 as empno from dual
union all
select '部门A' dname, 104 as empno from dual
union all
select '部门A' dname, 105 as empno from dual
union all
select '部门A' dname, 106 as empno from dual
union all
select '部门A' dname, 107 as empno from dual
union all
select '部门B' dname, 200 as empno from dual
union all
select '部门B' dname, 201 as empno from dual
union all
select '部门B' dname, 202 as empno from dual
union all
select '部门B' dname, 203 as empno from dual
union all
select '部门B' dname, 204 as empno from dual
union all
select '部门B' dname, 205 as empno from dual
union all
select '部门B' dname, 206 as empno from dual
union all
select '部门B' dname, 207 as empno from dual
),
emp as
(
select 100 as empno, 1 as lvl from dual
union all
select 101 as empno, 1 as lvl from dual
union all
select 102 as empno, 2 as lvl from dual
union all
select 103 as empno, 2 as lvl from dual
union all
select 104 as empno, 2 as lvl from dual
union all
select 105 as empno, 3 as lvl from dual
union all
select 106 as empno, 3 as lvl from dual
union all
select 107 as empno, 3 as lvl from dual
union all
select 200 as empno, 1 as lvl from dual
union all
select 201 as empno, 2 as lvl from dual
union all
select 202 as empno, 2 as lvl from dual
union all
select 203 as empno, 2 as lvl from dual
union all
select 204 as empno, 2 as lvl from dual
union all
select 205 as empno, 3 as lvl from dual
union all
select 206 as empno, 3 as lvl from dual
union all
select 207 as empno, 3 as lvl from dual
)
select lvl,
sum(case when dname = '部门A' then 1 else 0 end) as "部门A",
sum(case when dname = '部门B' then 1 else 0 end) as "部门B"
from dept a, emp b
where a.empno = b.empno
group by lvl;
LVL 部门A 部门B
---------- ---------- ----------
1 2 1
2 3 4
3 3 3
--类似例子
select job "职位",sum(f1) "部门1",sum(f2) "部门2",sum(f3) "部门3"
from
(select job,
case when deptno = 10 then 1 else 0 end f1,
case when deptno = 20 then 1 else 0 end f2,
case when deptno = 30 then 1 else 0 end f3
from
(select d.deptno, d.dname,job from emp e,dept d where e.deptno = d.deptno)
)
group by job;