[img]D:\a.bmp[/img]
如上图的一张表,我要统计operateid分别为OPER000001,OPER000002,OPER000003有多少记录且按照OPER000001,OPER000002,OPER000003的统计结果进行desc排序。代码如下:
其实也没什么,无非就是用到一点----查询本表的时候把其查询结果取个别名就OK,这个代码肯定可以优化,但现在做出来就好,我人比较笨,不过还是瞒高兴的。和大家一起分享^_^
执行的结果是:
[img]d:\b.bmp[/img]
如上图的一张表,我要统计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,这个代码肯定可以优化,但现在做出来就好,我人比较笨,不过还是瞒高兴的。和大家一起分享^_^
执行的结果是:
[img]d:\b.bmp[/img]