sql小节下下
select a.busiitemname busiitemname,
nvl(a.limitday,0) limitday,
nvl(f.slcounts,0) slcounts,
nvl(b.bjcounts,0) bjcounts,
nvl(c.thcounts,0) thcounts,
nvl(d.cucounts,0) cucounts,
nvl(e.avgdays,0) avgdays,
(select decode(r.isxzxk,'1','行政许可','2','非行政许可','3','其他','4','行政确认') from t_sys_permissionitem r where r.id=a.spitemid ) as servicetype,
case when bjcounts is null or slcounts is null then '0%' when bjcounts/slcounts=1 then '100%' else to_char(bjcounts*100/slcounts,'00.00')||'%' end bjlpercent,
limitday-nvl(e.avgdays,0) befors from
(select spitemid,id busiitemid,name busiitemname,limitday from t_sys_busiitem where status='1'
and spitemid in (select id from t_sys_permissionitem where status='1'
)) a left join
(select spitemid,busiitemid,count(id) bjcounts from t_busi_index where endtime is not null
group by spitemid,busiitemid) b on a.spitemid=b.spitemid and a.busiitemid=b.busiitemid left join
(select spitemid,busiitemid,count(id) thcounts from t_busi_index where endtime is not null and dealresult='2'
group by spitemid,busiitemid) c on a.spitemid=c.spitemid and a.busiitemid=b.busiitemid left join
(select spitemid,busiitemid,count(id) cucounts from t_busi_index where endtime is not null and useddays=0
group by spitemid,busiitemid) d on a.spitemid=d.spitemid and a.busiitemid=d.busiitemid left join
(select spitemid,busiitemid,avg(useddays) avgdays from t_busi_index where endtime is not null
group by spitemid,busiitemid) e on a.spitemid=e.spitemid and a.busiitemid=e.busiitemid left join
(select spitemid,busiitemid,count(id) slcounts from t_busi_index where starttime is not null
group by spitemid,busiitemid) f on a.spitemid=f.spitemid and a.busiitemid=f.busiitemid
两个看点，第一个：select decode(r.isxzxk,'1','行政许可','2','非行政许可','3','其他','4','行政确认') from t_sys_permissionitem r where r.id=a.spitemid,第一次接触这种写法
，在有聚合函数和分组的时候，如果想查其他的字段，这是一种方法，不然很难实现
第二个，别名定位问题： case when bjcounts is null or slcounts is null then '0%' when bjcounts/slcounts=1 then '100%' else to_char(bjcounts*100/slcounts,'00.00')||'%' end
中的bjcounts是b.bjcounts的bjcounts而不是别名bjcounts,这里sql和oracle都是这么的定位到底层，而不是定位到别名
create table spxingxiang1.datetest
(
date1 date default sysdate,
count1 number,
departname1 nvarchar2(100)
)
tablespace SP_XINGXIANG
storage
(
initial 64K
minextents 1
maxextents unlimited
);
select * from datetest
select extract(year from date1) as dateyear,extract(month from date1) as datemonth,count(count1) as count
from datetest group by extract(year from date1),extract(month from date1)
看点，这个sql实现了什么？可以按月，年分组聚合，比如你注册的部门从07-09年，你要统计每个月的部门注册数量，这个sql可以帮忙
select '受理数' lb,jbj,cls,sbs,hj from
(select count(id) jbj from t_busi_index where busiclass='1' and starttime is not null ) a,
(select count(id) cls from t_busi_index where busiclass='4' and starttime is not null ) b,
(select count(id) sbs from t_busi_index where busiclass='7' and starttime is not null ) c,
(select count(id) hj from t_busi_index where busiclass in('1','4','7') and starttime is not null ) d
union
select '办结数' lb,jbj,cls,sbs,hj from
(select count(id) jbj from t_busi_index where busiclass='1' and endtime is not null ) a,
(select count(id) cls from t_busi_index where busiclass='4' and endtime is not null ) b,
(select count(id) sbs from t_busi_index where busiclass='7' and endtime is not null) c,
(select count(id) hj from t_busi_index where busiclass in('1','4','7') and endtime is not null) d
union
select '特殊情况' lb,jbj,cls,sbs,hj from
(select count(id) jbj from t_busi_index where busiclass='1' and endtime is not null and dealresult in('2','3','5')) a,
(select count(id) cls from t_busi_index where busiclass='4' and endtime is not null and dealresult in('2','3','5')) b,
(select count(id) sbs from t_busi_index where busiclass='7' and endtime is not null and dealresult in('2','3','5')) c,
(select count(id) hj from t_busi_index where busiclass in('1','2','4','7') and endtime is not null and dealresult in('2','3','5')
) d
我刚开始的时候不知道还可以这么写，真是神奇
