最近在修改一个功能时,做了一个sql,感觉有些意思,写下来了。
select rownum no_0,
'MKJJJG0003' as rpt_code,
1602836 as vid,
173 as code,
suproj_lay_code as layer,
xid,
rownum as val, a.*
from (select t.*, d.xid
from tree_struture t
inner join data__store d on t.SUPROJ_CODE = d.val
and d.vid = 1602836
and d.sid = 1
and d.yid = 0
and t.pro_code = 'MKJJJG0003') a
start with a.parent_code = '0'
connect by prior a.UPROJ_CODE = a.PARENT_CODE
order siblings by a.suproj_order asc, a.id asc
说明: 要按照树形结构来取的数据,但是tree_struture 表中却没有data__store表中的数据,做内关联之后,形成一个新的带有数据存储字段的数据集,然后,再通过子查询,按照树形结构的排序方式,取得需要的树型。这个sql最主要的通过子查询,按照需要形成数据子集,再对这个数据子集做树型结构化
select rownum no_0,
'MKJJJG0003' as rpt_code,
1602836 as vid,
173 as code,
suproj_lay_code as layer,
xid,
rownum as val, a.*
from (select t.*, d.xid
from tree_struture t
inner join data__store d on t.SUPROJ_CODE = d.val
and d.vid = 1602836
and d.sid = 1
and d.yid = 0
and t.pro_code = 'MKJJJG0003') a
start with a.parent_code = '0'
connect by prior a.UPROJ_CODE = a.PARENT_CODE
order siblings by a.suproj_order asc, a.id asc
说明: 要按照树形结构来取的数据,但是tree_struture 表中却没有data__store表中的数据,做内关联之后,形成一个新的带有数据存储字段的数据集,然后,再通过子查询,按照树形结构的排序方式,取得需要的树型。这个sql最主要的通过子查询,按照需要形成数据子集,再对这个数据子集做树型结构化