oracle查询行变列的实现.docx

1、 --每个部门各种工作的人数 --普通 select deptno,job,count(*) from emp group by deptno,job order by deptno --行变列 select deptno, count(decode(job,CLERK,job,null)) CLERK, count(decode(job,SALESMAN,job,null)) SALESMAN, count(decode(job,MANAGER,job,null)) MANAGER, count(decode(job,PRESIDENT,job,null)) PRESIDENT, count(decode(job,ANALYST,job,null)) ANALYST, count(*) 总人数 from emp group by deptno order by deptno 2、 --每种工作对应的工资级别的人数 select job, count(case when sal700 and sal=1200 then sal else null end) 一级, count(case when sal1200 and sal=1400 then sal else null end) 二级, count(case when sal1400 and sal=2000 then sal else null end) 三级, count(case when sal2000 and sal=3000 then sal else null end) 四级, count(case when sal3000 and sal=9999 then sal else null end) 五级, count(*) 总人数 from emp group by job order by decode(job,PRESIDENT,A, MANAGER,B, ANALYST,C, SALESMAN,D, CLERK,E) select job, decode(job,PRESIDENT,A, MANAGER,B, ANALYST,C, SALESMAN,D, CLERK,E) grade, count(case when sal700 and sal=1200 then sal else null end) 一级, count(case when sal1200 and sal=1400 then sal else null end) 二级, count(case when sal1400 and sal=2000 then sal else null end) 三级, count(case when sal2000 and sal=3000 then sal else null end) 四级, count(case when sal3000 and sal=9999 then sal else null end) 五级, count(*) 总人数 from emp group by job order by grade 3、 --每个部门80年、81年、82年、82年以后入职人数 select dname, count(decode(to_char(hiredate,YYYY), hiredate, null)) 1980, 1980, count(decode(to_char(hiredate,YYYY), 1981, hiredate, null)) 1981, count(decode(to_char(hiredate,YYYY), 1982, hiredate, null)) 1982, count(case when to_char(hiredate,YYYY)1982 then hiredate else null end) 1982后 from emp,dept where emp.deptno=dept.deptno group by dname 崎置倡勹 崎置倡勹—f — 2 啊 E ] 1 980 . 3 SALES | 斗—－ 旦＿—