oracle 查询汇总

1.查询所有父节点

select * from (select sys_connect_by_path(dept_name,'-')pname1,sys_connect_by_path(dept_id,'-') pname from web_dept_a start with dept_id = '001.001.045.001.005' connect by prior pre_dept_id = dept_id) where pname <> '-001.001.045.001.005'

 

2.分页查询

SELECT id,title,add_date,source,RELEASE_STATE FROM (SELECT DJ.id,DJ.add_date,DJ.title,DJ.source,DJ.RELEASE_STATE,ROWNUM RN FROM (SELECT id,title,add_date,source,RELEASE_STATE FROM ARTICLE where RELEASE_STATE = 'immediately' and is_del='false' and  ARTICLE_CLASS_id= ? ORDER BY add_date desc) DJ ) WHERE RN BETWEEN ? AND ?;

 

3.查询当前节点的下一级

SELECT (select count(1) from web_dept_a a where a.PRE_DEPT_ID =b.DEPT_ID) as con,dept_id,dept_name,pre_dept_id from web_dept_a b where pre_dept_id = ?

 

4.分组排序获取第一条(https://blog.