具体的sql为
select level treelevel,
eva.state,
t.id,
case
when t.parentid is null then
''
else
t.parentid || ''
end parentid,
t.summary,
t.score,
t.content,
t.standardscore,
t.selfAssessmentScore,
t.remark,
t.scoreReason,
t.ruleType,
case
when (select count(1) from
abc dt where dt.parentid = t.id) = 0 then
'1'
else
'0'
end leaf,
case
when level = 3 then
'0'
else
'1'
end expanded
from
abc t,
def eva
where t.defid = eva.id
and t.defid = #value#
start with t.id in (select id
from
abc d
where d.parentid is null
and d.defid = #value#)
connect by t.parentid = prior t.id ORDER SIBLINGS BY t.seq
****************************************************************
这段sql实现的是一个树形表,使用start with connect by prior level来实现,这个树有两张表:abc和def,abc是def的子表(abc.defid = def.id)。def存的是这个树叫什么名字,是哪个单位,哪个月份的树,abc存的是这个树具体的细则。
leaf和expanded映射成javaBean后是两个boolean类型的值,用来判断图标。
因此,排除def的干扰后,由abc的递归查询来实现这颗树:
start with t.id in (select id
from abc d
where d.parentid is null
and d.defid = #value#)
connect by t.parentid = prior t.id ORDER SIBLINGS BY t.seq
首先确定递归查询的范围:
select id
from abc d
where d.parentid is null
and d.defid = #value#
defid=#value#这棵树的所有顶层节点(因为这颗树只有两层)
递归查询的下次的parentid是上次的id,根据seq字段进行兄弟姐妹排序(ORDER SIBLINGS BY)
由connect by生成的系统默认字段level来生成expanded字段的值
分享到:
相关推荐
Enterprise Application Architecture with .NET Core by Ganesan Senthilvel English | 25 Apr. 2017 | ASIN: B01M18CQNP | 564 Pages | AZW3 | 9.94 MB Architect and design highly scalable, robust, clean and...
A practical guide with clear instructions to design and develop a complete web application from start to finish Who This Book Is For This book is designed for JavaScript developers of any skill level ...
select * from table1 c start with c.p_id='0000000' connect by prior c.id=c.p_id and c.use_yn='Y' order by id ; 2. 查询节点中所有的层级关系 SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",...
We start with the basics of networking and then explore how Java supports the development of client/server and peer-to-peer applications. The NIO packages are examined as well as multitasking and how ...
We start with the basics of networking and then explore how Java supports the development of client/server and peer-to-peer applications. The NIO packages are examined as well as multitasking and how ...
The ideal reader will approach this book after completing Beginning BlackBerry Development, but intermediate-level programmers may start directly with this book. Readers are expected to have some ...
当你用start with connect by nocycle prior 进行递归查找数据的时候那么下面两段代码的性能肯定是有明显差别的大家用的时候 请注意了代码可以不看下面 直接看我的总结 //查询某个文件夹文件夹ID=12里面的层次数以及...
This book is a comprehensive beginner level tutorial on PostgreSQL and introduces the features of the newest version 10, along with explanation of concepts in a very easy to understand manner....
This course starts with making you familiar with its features and enable you to develop and enhance your dashboard skills, starting with an overview of what dashboard is, followed by how you can ...
本文介绍Oracle中使用START WITH...CONNECT BY PRIOR子句实现递归查询树形结构的方法,小伙伴们可以参考一下。
If a Feature Stop cannot be correlated to a Feature Start on the same thread, it is matched with a Feature Start of the same name without regard to thread (if one exists). Resolved Issues Concerning:...
格式: SELECT column FROM table_name START WITH column=value CONNECT BY PRIOR 父主键=子外键 select lpad(‘ ‘,4*(level-1))||name name,job,id,super from emp start with super is null connect by prior id...
The process will start with creation of test coverage for the existing code and from there on, we'll be able to start refactoring until both the tests and the code meet our expectations. Chapter 10, ...
The integrated debugger for VBScript, JScript and PowerShell can now also connect to a remote machine and enable you to debug your code on the intended target system. No more guesswork and writing ...
Accumulo has been proven to be able to handle petabytes of data, with cell-level security, and real-time analyses so this is your step by step guide in taking full advantage of this power. ...
This book will help you broaden your horizons by taking your programming skills to next level. The initial chapters will show you all about the environment that surrounds a developer at the start of a...
Source Code for Hello World with Pygame ................................................................................ 7 Setting Up a Pygame Program ....................................................
(HEX File), then press [F10] to start the program. The software then starts writing the main program to the transceiver flash memory. Use this function for tasks such as upgrading the transceiver. ...
To make use of either more or less strict isolation levels in applications, locking can be customized for an entire session by setting the isolation level of the session with the SET TRANSACTION ...