将多条数据以横向排列生成报表

版权声明:原创文章,转载请注明出处! https://blog.csdn.net/SSDN_Pang/article/details/49781315

将多条数据以横向排列部门: “部门号”、“部门名称”,工资分段:“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;

生成结果

展开阅读全文

没有更多推荐了,返回首页