【原创】SQL栏目树的代码
本文由网友whl供稿,特此感谢!
/**
* Desc: 取栏目树 ,过滤用户权限和无效栏目
* Author: WHL
* Date: 2009-05-31 15:17
*/
/** 1. 取某用户有权限(np_cms_column_security表有记录且t.action_1 = ‘1’)的栏目的树 **/
create or replace view V_NP_CTREE_BS as
select B.* from (
select A.*, lag(A.column_id) over(partition by A.column_id order by 0 ) RK
from (select /+choose /
t.*
from np_cms_column t
where t.is_active = '1'
connect by prior t.column_id = t.parent_id
start with t.column_id in (select t.column_id
from np_cms_column_security t
where t.subject_id = 'mazj'
/这里添加角色过滤/
and t.action_1 = '1'))A) B
where not exists
(select 0
from (select distinct d.column_id
from np_cms_column d
connect by prior d.column_id = d.parent_id
start with d.column_id in
(select t.column_id
from np_cms_column_security t
where t.subject_id = 'mazj'
/ 这里添加角色过滤/
and t.action_1 = '0'
/ 排除有权限树下的非授权ID,既 Action_1=0的/
and exists
(select 0
from (select distinct d.column_id
from np_cms_column d
connect by prior d.column_id =
d.parent_id
start with d.column_id in
(select t.column_id
from np_cms_column_security t
where t.subject_id =
'mazj'
/这里添加角色过滤/
and t.action_1 = '1')) C1
where C1.column_id = t.column_id))
and d.is_active = '1') C
where C.column_id = B.column_id and B.RK is null) and B.RK is null
union all
select c.*, 0 RK from np_cms_column c where c.parent_id = 0;
————————————————————————
/** 2.得到栏目的虚拟父亲ID(考虑到把断层的节点接起来)**/
create or replace view V_NP_CTREE_PA as
select B.*,
(case B.column_id
when 1 then 0 else nvl(B.father, 1) end) VFA
from (select v.*,
(select vv.column_id
from V_NP_CTREE_BS vv
where vv.column_id = v.parent_id) FATHER
from V_NP_CTREE_BS v) B;
————————————————————————
/** 3. 取出门户需要的栏目树 **/
--create or replace view V_NP_CTREE_RS as
select
D., LPAD(' ', 2 level - 1) || SYS_CONNECT_BY_PATH(D.COLUMN_NAME, '/') "Path"
from (select c.*
from V_NP_CTREE_PA c
order by c.VFA, c.disorder desc, c.column_id desc) D
connect by prior D.column_id = D.VFA
start with D.column_id = 1;
————————————————————————
(本文版权由whl所,转载时请注明作者和出处)
转载于酷壳CoolShell 无删改 仅以此纪念陈皓(左耳朵耗子)
这个价格我不太信国内能原价买到 想买但是买不到啊 等 GTA6 出了再配不迟。 国行 5090D 价格出了,16499 。 我手上有个 4090 想置换,不…
昨天,Gitlab.com发生了一个大事,某同学误删了数据库,这个事看似是个低级错误,不过,因为Gitlab把整个过程的细节都全部暴露出来了,所以,可以看到很多东西,而对于类似…
用途相对单一是指,要运行的程序一共只有四个。一个交易软件,用 Java 写的。一个 Dropbox ,一个 Goodsync ,一个坚果云。偶尔打开证券公司官网下载一下报表。要…