程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL 實例代碼:取欄目樹,過濾用戶權限和無效欄目

SQL 實例代碼:取欄目樹,過濾用戶權限和無效欄目

編輯:關於SqlServer

    文由網友whl供稿,特此感謝!
/**
  * Desc: 取欄目樹 ,過濾用戶權限和無效欄目
  * Author: WHL
  * Date: 2009-05-31 15:17
  */


/** 1. 取某用戶有權限(np_cms_column_security表有記錄且t.action_1 = ‘1′)的欄目的樹 **/
01.createorreplacevIEwV_NP_CTREE_BSas
02.selectB.*from(
03.selectA.*, lag(A.column_id) over(partitionbyA.column_idorderby0 ) RK
04.  from(select/*+choose */
05.         t.*
06.          fromnp_cms_column t
07.         wheret.is_active ='1'
08.        connectbypriort.column_id = t.parent_id
09.         startwitht.column_idin(selectt.column_id
10.                                      fromnp_cms_column_security t
11.                                     wheret.subject_id ='mazj'
12.                                          /*這裡添加角色過濾*/
13.                                       andt.action_1 ='1'))A) B
14. wherenotexists
15. (select0
16.          from(selectdistinctd.column_id
17.                  fromnp_cms_column d
18.                connectbypriord.column_id = d.parent_id
19.                 startwithd.column_idin
20.                    (selectt.column_id
21.                       fromnp_cms_column_security t
22.                      wheret.subject_id ='mazj'
23.                           /* 這裡添加角色過濾*/
24.                        andt.action_1 ='0'
25.                           /* 排除有權限樹下的非授權ID,既 Action_1=0的*/
26.                        andexists
27.                      (select0
28.                               from(selectdistinctd.column_id
29.                                       fromnp_cms_column d
30.                                     connectbypriord.column_id =
31.                                                 d.parent_id
32.                                      startwithd.column_idin
33.                                                 (selectt.column_id
34.                                                    fromnp_cms_column_security t
35.                                                   wheret.subject_id =
36.                                                         'mazj'
37.                                                        /*這裡添加角色過濾*/
38.                                                     andt.action_1 ='1')) C1
39.                              whereC1.column_id = t.column_id))
40.                        andd.is_active ='1') C
41.         whereC.column_id = B.column_idandB.RKisnull)andB.RKisnull
42.unionall
43.selectc.*, 0 RKfromnp_cms_column cwherec.parent_id = 0;
————————————————————————
/** 2.得到欄目的虛擬父親ID(考慮到把斷層的節點接起來)**/

01.createorreplacevIEwV_NP_CTREE_PAas
02.selectB.*,
03.       (caseB.column_id
04.         when1then0elsenvl(B.father, 1)end) VFA
05.  from(selectv.*,
06.               (selectvv.column_id
07.                  fromV_NP_CTREE_BS vv
08.                 wherevv.column_id = v.parent_id) FATHER
09.          fromV_NP_CTREE_BS v) B;
————————————————————————
/** 3. 取出門戶需要的欄目樹 **/

1.--create or replace vIEw V_NP_CTREE_RS as
2.select
3. D.*, LPAD(' ', 2 *level- 1) || SYS_CONNECT_BY_PATH(D.COLUMN_NAME,'/') "Path"
4.  from(selectc.*
5.          fromV_NP_CTREE_PA c
6.         orderbyc.VFA, c.disorderdesc, c.column_iddesc) D
7.connectbypriorD.column_id = D.VFA
8. startwithD.column_id = 1;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved