Note
The SQL take me almost half a day, which construct the data as a tree, an statistic data by the tree's full path, finally, show it in a tree.
SQL
- select LEVEL TREELEVEL,
- Z2.TYPE_NAME_SHOT,
- Z1.TOTAL_INVEST_NEW,
- Z1.YEAR_INVEST_NEW,
- Z1.CARRY_OVER_INVEST_NEW,
- Z1.TOTAL_INVEST_CON,
- Z1.YEAR_INVEST_CON,
- Z1.CARRY_OVER_INVEST_CON,
- Z1.TOTAL_INVEST_SUM,
- Z1.YEAR_INVEST_SUM,
- Z1.CARRY_OVER_INVEST_SUM
- from (SELECT TREE.BO_INVEST_FRAME_ID,
- TREE.NAME,
- SUM(x.TOTAL_INVEST_NEW) TOTAL_INVEST_NEW,
- SUM(x.YEAR_INVEST_NEW) YEAR_INVEST_NEW,
- SUM(x.CARRY_OVER_INVEST_NEW) CARRY_OVER_INVEST_NEW,
- SUM(x.TOTAL_INVEST_CON) TOTAL_INVEST_CON,
- SUM(x.YEAR_INVEST_CON) YEAR_INVEST_CON,
- SUM(x.CARRY_OVER_INVEST_CON) CARRY_OVER_INVEST_CON,
- SUM(x.TOTAL_INVEST_SUM) TOTAL_INVEST_SUM,
- SUM(x.YEAR_INVEST_SUM) YEAR_INVEST_SUM,
- SUM(x.CARRY_OVER_INVEST_SUM) CARRY_OVER_INVEST_SUM
- from (
- -- statitsc data
- select SYS_CONNECT_BY_PATH(v.BO_INVEST_FRAME_ID, '^') FULLPATH,
- LEVEL TREELEVEL,
- V.type_name_shot,
- v.bo_invest_frame_id,
- v.parent_type_shot,
- v.TOTAL_INVEST_NEW,
- v.YEAR_INVEST_NEW,
- v.CARRY_OVER_INVEST_NEW,
- v.TOTAL_INVEST_CON,
- v.YEAR_INVEST_CON,
- v.CARRY_OVER_INVEST_CON,
- v.TOTAL_INVEST_SUM,
- v.YEAR_INVEST_SUM,
- v.CARRY_OVER_INVEST_SUM
- from (select v1.type_name_shot,
- v1.parent_type_shot,
- v1.bo_invest_frame_id,
- v1.sort_weight_shot,
- v2.*,
- v3.*,
- v4.*
- from -- typic tree
- (SELECT t.bo_invest_frame_id,
- t.type_name_shot,
- t.parent_type_shot,
- t.sort_weight_shot
- FROM BO_INVEST_FRAME_SHOT t
- WHERE BO_INVEST_PLAN_ID =
- (select t.bo_invest_plan_id
- from bo_invest_plan_ver t
- where t.bo_invest_plan_ver_id =
- 'd53abea7-fa03-437a-91d0-cacaa0c49849')
- AND INVEST_FRAME_TYPE = '1'
- AND TYPE_NAME_SHOT <> '其中：零星购置') v1,
- ( -- case one
- select tt.project_type,
- TO_CHAR(SUM(TT.TOTAL_INVEST),
- 'FM99999999999.99') TOTAL_INVEST_NEW,
- TO_CHAR(SUM(TT.YEAR_INVEST),
- 'FM99999999999.99') YEAR_INVEST_NEW,
- TO_CHAR(SUM(TT.CARRY_OVER_INVEST),
- 'FM99999999999.99') CARRY_OVER_INVEST_NEW
- from (select DECODE(t1.CARRY_OVER_FLAG,
- '1',
- '结转项目',
- '2',
- '新开工项目') CARRY_OVER_FLAG_NAME,
- t1.project_type,
- t1.CARRY_OVER_FLAG,
- t1.cancel_flag,
- t1.delete_flag,
- t1.bo_invest_plan_ver_id,
- t1.carry_over_invest,
- t1.total_invest,
- t1.year_invest
- from bo_project_ver t1
- where t1.cancel_flag = '2'
- and t1.delete_flag = '2'
- and t1.parent_project = '0'
- and t1.bo_invest_plan_ver_id =
- 'd53abea7-fa03-437a-91d0-cacaa0c49849') TT
- where tt.CARRY_OVER_FLAG = '2'
- group by tt.project_type) v2,
- ( -- case two
- select tt.project_type,
- TO_CHAR(SUM(TT.TOTAL_INVEST),
- 'FM99999999999.99') TOTAL_INVEST_CON,
- TO_CHAR(SUM(TT.YEAR_INVEST),
- 'FM99999999999.99') YEAR_INVEST_CON,
- TO_CHAR(SUM(TT.CARRY_OVER_INVEST),
- 'FM99999999999.99') CARRY_OVER_INVEST_CON
- from (select DECODE(t1.CARRY_OVER_FLAG,
- '1',
- '结转项目',
- '2',
- '新开工项目') CARRY_OVER_FLAG_NAME,
- t1.project_type,
- t1.CARRY_OVER_FLAG,
- t1.cancel_flag,
- t1.delete_flag,
- t1.bo_invest_plan_ver_id,
- t1.carry_over_invest,
- t1.total_invest,
- t1.year_invest
- from bo_project_ver t1
- where t1.cancel_flag = '2'
- and t1.delete_flag = '2'
- and t1.parent_project = '0'
- and t1.bo_invest_plan_ver_id =
- 'd53abea7-fa03-437a-91d0-cacaa0c49849') TT
- where tt.CARRY_OVER_FLAG = '1'
- group by tt.project_type) v3,
- ( -- case three
- select tt.project_type,
- TO_CHAR(SUM(TT.TOTAL_INVEST),
- 'FM99999999999.99') TOTAL_INVEST_SUM,
- TO_CHAR(SUM(TT.YEAR_INVEST),
- 'FM99999999999.99') YEAR_INVEST_SUM,
- TO_CHAR(SUM(TT.CARRY_OVER_INVEST),
- 'FM99999999999.99') CARRY_OVER_INVEST_SUM
- from (select DECODE(t1.CARRY_OVER_FLAG,
- '1',
- '结转项目',
- '2',
- '新开工项目') CARRY_OVER_FLAG_NAME,
- t1.project_type,
- t1.CARRY_OVER_FLAG,
- t1.cancel_flag,
- t1.delete_flag,
- t1.bo_invest_plan_ver_id,
- t1.carry_over_invest,
- t1.total_invest,
- t1.year_invest
- from bo_project_ver t1
- where t1.cancel_flag = '2'
- and t1.delete_flag = '2'
- and t1.parent_project = '0'
- and t1.bo_invest_plan_ver_id =
- 'd53abea7-fa03-437a-91d0-cacaa0c49849') TT
- group by tt.project_type) v4
- where v1.bo_invest_frame_id = v2.project_type(+)
- and v1.bo_invest_frame_id = v3.project_type(+)
- and v1.bo_invest_frame_id = v4.project_type(+)) V
- START WITH V.PARENT_TYPE_SHOT = '0'
- CONNECT BY PRIOR V.BO_INVEST_FRAME_ID = V.PARENT_TYPE_SHOT
- ORDER SIBLINGS BY V.SORT_WEIGHT_SHOT) x,
- -- typic tree with full path
- (SELECT SYS_CONNECT_BY_PATH(T.BO_INVEST_FRAME_ID, '^') FULLPATH,
- T.BO_INVEST_FRAME_ID ORGANIZATIONID,
- T.BO_INVEST_FRAME_ID,
- T.TYPE_NAME_SHOT NAME,
- T.SORT_WEIGHT_SHOT,
- T.PARENT_TYPE_SHOT PARENTORGANIZATIONID
- FROM (SELECT *
- FROM BO_INVEST_FRAME_SHOT
- WHERE BO_INVEST_PLAN_ID =
- (select t.bo_invest_plan_id
- from bo_invest_plan_ver t
- where t.bo_invest_plan_ver_id =
- 'd53abea7-fa03-437a-91d0-cacaa0c49849')
- AND INVEST_FRAME_TYPE = '1'
- AND TYPE_NAME_SHOT <> '其中：零星购置') T
- CONNECT BY PRIOR T.BO_INVEST_FRAME_ID = T.PARENT_TYPE_SHOT
- START WITH T.PARENT_TYPE_SHOT = '0'
- ORDER SIBLINGS BY T.SORT_WEIGHT_SHOT) TREE
- WHERE x.FULLPATH LIKE TREE.FULLPATH || '%'
- GROUP BY TREE.BO_INVEST_FRAME_ID, TREE.NAME) Z1,
- --typic tree
- (SELECT T.BO_INVEST_FRAME_ID ORGANIZATIONID,
- T.BO_INVEST_FRAME_ID,
- T.TYPE_NAME_SHOT,
- T.SORT_WEIGHT_SHOT,
- T.PARENT_TYPE_SHOT
- FROM (SELECT *
- FROM BO_INVEST_FRAME_SHOT
- WHERE BO_INVEST_PLAN_ID =
- (select t.bo_invest_plan_id
- from bo_invest_plan_ver t
- where t.bo_invest_plan_ver_id =
- 'd53abea7-fa03-437a-91d0-cacaa0c49849')
- AND INVEST_FRAME_TYPE = '1'
- AND TYPE_NAME_SHOT <> '其中：零星购置') T) Z2
- where Z1.BO_INVEST_FRAME_ID = Z2.BO_INVEST_FRAME_ID
- CONNECT BY PRIOR Z2.BO_INVEST_FRAME_ID = Z2.PARENT_TYPE_SHOT
- START WITH Z2.PARENT_TYPE_SHOT = '0'
- ORDER SIBLINGS BY Z2.SORT_WEIGHT_SHOT;
- /*
- * daniel zhou 2010-09-29 17:22
- */
本文转自danni505 51CTO博客，原文链接：http://blog.51cto.com/danni505/399242，如需转载请自行联系原作者
