oracle 时间按半小时递归,oracle带年份的树形递归统计

-------------------递归统计出每个企业以及其所属企业在每一年的数据总和(最终结果)

select 年度,企业ID,

(select sum(数量) from test_temp t2 connect by prior 企业ID=所属企业ID and 年度=t.年度 start with 企业ID=t.企业ID and 年度=t.年度) as 数量

,min(所属企业ID) as 所属企业ID from (

-------------------------------->>>>>>>>>>>>>>>>>>>>>>>>>>非递归统计开始(查询出每个企业在每一年的数据)

select moment 年度,eid 企业ID,con 数量,belongto 所属企业ID from(

select xx.moment,xx.eid,xx.con,yy.belongto from

(select a.moment,a.eid,decode(c.con,null,0,c.con) con

from

------分解所选年份和所选企业为二维表

(select distinct y.moment,b.eid from

(select moment from (select level moment from dual connect by level <= to_number(substr('2013-12-31',1,4)))where moment >= to_number(substr('2012-01-01',1,4)) and moment <= to_number(substr('2013-12-31',1,4))) y,

(select column_value eid from table(cast (fn_split ('1,41,53,59,58,48,129,130,69,11,12,13,111,46,60,55,82,131,174,112,57,14306', ',') as ty_str_split))) b

) a,

------查询出每个企业在每个年份的统计数量

(select coff1,au,sum(con) con from(select coff1,to_char(AUDITTIMELAST,'YYYY') au,count(*) con from cmis_quality_cul_exp group by coff1,AUDITTIMELAST) group by coff1,au) c

where a.moment = c.au(+) and a.eid = c.coff1(+) order by moment

) xx,

--获取所属层级

(SELECT ID,belongto FROM TS_DEPARTMENT WHERE enterprisetype = 1) yy

where xx.eid = yy.id

) order by moment,eid

-------------------------------->>>>>>>>>>>>>>>>>>>>>>>>>>非递归统计结束

) t group by 企业ID,年度 order by 年度,企业ID