【原创】SQL栏目树的代码

【原创】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 ,请勿用于任何商业用途)

好烂啊有点差凑合看看还不错很精彩 (5 人打了分,平均分: 2.60 )
Loading...

【原创】SQL栏目树的代码》 的相关评论

  1. 看到一个这么长的sql,起初就会想起数据表设计不合理了,还有谁想直接在这上面维护。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注