Perfect-World

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

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;

1

2

3

4

5

6

7

8

9

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;

1

2

3

4

5

6

7

8

9

--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;