将多条数据以横向排列部门: “部门号”、“部门名称”,工资分段:“1-1000”、“1001-2000”、“2001-3000”、“3001-4000”、“4001-5000” 生成报表。
数据来自Oracle 中scott 用户的emp表、dept表
sql语句如下:
select
t1.deptno "部门号",t2.dname "部门名",
count(case when sal BETWEEN 1 AND 1000 then '1-1000' else null end)"1-1000",
count(case when sal BETWEEN 1001 AND 2000 then '1001-2000' else null end)"1001-2000",
count(case when sal BETWEEN 2001 AND 3000 then '1001-2000' else null end)"2001-3000",
count(case when sal BETWEEN 3001 AND 4000 then '1001-2000' else null end)"3001-4000",
count(case when sal BETWEEN 4001 AND 5000 then '1001-2000' else null end)"4001-5000"
from emp t1,dept t2
where t1.deptno=t2.deptno
group by t1.deptno,t2.dname;