Oracle的left join和group by使用起来秒不可言


如上图的一张表，我要统计operateid分别为OPER000001,OPER000002,OPER000003有多少记录且按照OPER000001，OPER000002，OPER000003的统计结果进行desc排序。代码如下：

select a.id_item as id_item,a.operatenum as operateid,nvl(b.operatenum1,0) as operatenum1,nvl(c.operatenum2,0) as operatenum2 from(select id_item,count(operateid) as operatenum from stm_expapprove where operateid='OPER000001'  group by(id_item)  order by operatenum desc)a 
left join (select id_item,count(operateid) as operatenum1 from stm_expapprove where operateid='OPER000002'  group by(id_item)  order by operatenum1 desc)b on a.id_item=b.id_item left join 
(select id_item,count(operateid) as operatenum2 from stm_expapprove where operateid='OPER000003'  group by(id_item)  order by operatenum2 desc)c on b.id_item=c.id_item order by operateid desc,operatenum1 desc,operatenum2 desc

其实也没什么，无非就是用到一点----查询本表的时候把其查询结果取个别名就OK，这个代码肯定可以优化，但现在做出来就好，我人比较笨，不过还是瞒高兴的。和大家一起分享^_^

执行的结果是：