下面是分享的sql,sql并不是本人亲自写出来而是请别人帮忙编码,事后问起他为什么会写这么复杂的sql 给我的回答是:多写一点就会了,期待oracle的高级查询方面有所增强 decode的用法来自:http://blog.csdn.net/u010924897/article/details/47066937 小计的算法来自:http://blog.csdn.net/lqh4188/article/details/7597117 select dwmc,case when zymc is null then '系部各年级人数合计' else zymc end yname ,sum("2014") njone, sum("2015")njtwo, sum("2016")njthree,sum("2017")njfire,sum(allNum) allNum from( select dwmc,zymc,zydm ,(case when min(decode(xznj,'2014',yxnum,null)) is null then 0 else min(decode(xznj,'2014',yxnum,null)) end) as "2014" ,(case when min(decode(xznj,'2015',yxnum,null)) is null then 0 else min(decode(xznj,'2015',yxnum,null))