oracle:
select DISTINCT a.sys_id, a.sub_id, a.mod_id
from (
select distinct C.sys_id, C.sub_id, C.mod_id, C.fmod_id, 1 lv
from PCT_USER_PERMISSION a,
PCT_PROGRAM P,
PCT_MODULE c
where a.sys_id =P.sys_id and a.sub_id =P.sub_id and a.mod_id =P.mod_id AND A.PROG_ID=P.PROG_ID
AND P.sys_id =c.sys_id and P.sub_id =c.sub_id and P.mod_id =c.mod_id
and P.PROG_STATUS <> '2' AND a.fact_no = 'LB'
and upper(a.usr_id) = upper('CN')
and a.sys_id = '01'
and (a.SUB_ID = '0030' OR '0030' = '%')
and a.mark <> '-'
UNION
select distinct c.sys_id, c.sub_id, c.mod_id,c.fmod_id, 1 lv
from PCT_GROUP_PERMISSION A,PCT_PROGRAM p,
PCT_GROUP B,
PCT_MODULE c
where A.FACT_NO = B.FACT_NO
AND A.GROUP_ID = B.GROUP_ID
AND a.sys_id =P.sys_id and a.sub_id =P.sub_id and a.mod_id =P.mod_id AND A.PROG_ID=P.PROG_ID
and a.sys_id =c.sys_id and a.sub_id =c.sub_id and a.mod_id =c.mod_id
and P.PROG_STATUS <> '2' AND A.fact_no = 'LB'
and A.group_id = '0030020140'
and A.sys_id = '01'
and (a.SUB_ID = '0030' OR '0030' = '%')
AND isnull(B.STOP_YN, 'N') = 'N'
union all
select a.SYS_ID,a.SUB_ID,a.MOD_ID,a.FMOD_ID,0 lv
from PCT_MODULE a
where a.SYS_ID = '01'
AND (a.SUB_ID = '0030' OR '0030' = '%')
) a
CONNECT BY PRIOR a.FMOD_ID = a.MOD_ID AND A.SUB_ID = prior A.SUB_ID
START WITH lv =1
如何才可以换成mssql2008的语法呢
------解决思路----------------------
WITH a (sys_id, sub_id, mod_id, fmod_id, lv) AS (
SELECT *
FROM (
select distinct C.sys_id, C.sub_id, C.mod_id, C.fmod_id, 1 lv
from PCT_USER_PERMISSION a,
PCT_PROGRAM P,
PCT_MODULE c
where a.sys_id =P.sys_id and a.sub_id =P.sub_id and a.mod_id =P.mod_id AND A.PROG_ID=P.PROG_ID
AND P.sys_id =c.sys_id and P.sub_id =c.sub_id and P.mod_id =c.mod_id
and P.PROG_STATUS <> '2' AND a.fact_no = 'LB'
and upper(a.usr_id) = upper('CN')
and a.sys_id = '01'
and (a.SUB_ID = '0030' OR '0030' = '%')
and a.mark <> '-'
UNION
select distinct c.sys_id, c.sub_id, c.mod_id,c.fmod_id, 1 lv
from PCT_GROUP_PERMISSION A,PCT_PROGRAM p,
PCT_GROUP B,
PCT_MODULE c
where A.FACT_NO = B.FACT_NO
AND A.GROUP_ID = B.GROUP_ID
AND a.sys_id =P.sys_id and a.sub_id =P.sub_id and a.mod_id =P.mod_id AND A.PROG_ID=P.PROG_ID
and a.sys_id =c.sys_id and a.sub_id =c.sub_id and a.mod_id =c.mod_id
and P.PROG_STATUS <> '2' AND A.fact_no = 'LB'
and A.group_id = '0030020140'
and A.sys_id = '01'
and (a.SUB_ID = '0030' OR '0030' = '%')
AND isnull(B.STOP_YN, 'N') = 'N'
) a1
UNION ALL
SELECT a2.*
FROM (
select a.SYS_ID,a.SUB_ID,a.MOD_ID,a.FMOD_ID,0 lv
from PCT_MODULE a
where a.SYS_ID = '01'
AND (a.SUB_ID = '0030' OR '0030' = '%')
) a2
JOIN a
ON a.FMOD_ID = a2.MOD_ID
AND a.SUB_ID = a2.SUB_ID
)
select DISTINCT a.sys_id, a.sub_id, a.mod_id
FROM a
------解决思路----------------------
MS SQL可树形递归是用CTE,楼主自己测试一下以下语句是否正确
;WITH Cte
AS
(
select distinct C.sys_id, C.sub_id, C.mod_id, C.fmod_id, 1 lv
from PCT_USER_PERMISSION a,
PCT_PROGRAM P,
PCT_MODULE c
where a.sys_id =P.sys_id and a.sub_id =P.sub_id and a.mod_id =P.mod_id AND A.PROG_ID=P.PROG_ID
AND P.sys_id =c.sys_id and P.sub_id =c.sub_id and P.mod_id =c.mod_id
and P.PROG_STATUS <> '2' AND a.fact_no = 'LB'
and upper(a.usr_id) = upper('CN')
and a.sys_id = '01'
and (a.SUB_ID = '0030' OR '0030' = '%')
and a.mark <> '-'
UNION
select distinct c.sys_id, c.sub_id, c.mod_id,c.fmod_id, 1 lv
from PCT_GROUP_PERMISSION A,PCT_PROGRAM p,
PCT_GROUP B,
PCT_MODULE c
where A.FACT_NO = B.FACT_NO
AND A.GROUP_ID = B.GROUP_ID
AND a.sys_id =P.sys_id and a.sub_id =P.sub_id and a.mod_id =P.mod_id AND A.PROG_ID=P.PROG_ID
and a.sys_id =c.sys_id and a.sub_id =c.sub_id and a.mod_id =c.mod_id
and P.PROG_STATUS <> '2' AND A.fact_no = 'LB'
and A.group_id = '0030020140'
and A.sys_id = '01'
and (a.SUB_ID = '0030' OR '0030' = '%')
AND isnull(B.STOP_YN, 'N') = 'N'
union all
select a.SYS_ID,a.SUB_ID,a.MOD_ID,a.FMOD_ID,0 lv
from PCT_MODULE a
where a.SYS_ID = '01'
AND (a.SUB_ID = '0030' OR '0030' = '%')
),Cte2
AS
(
SELECT * FROM Cte WHERE Lv=2
UNION ALL
SELECT b.* FROM Cte2 AS a INNER JOIN Cte AS b ON a.FMOD_ID=b.MOD_ID AND a.SUB_ID=b.SUB_ID
)
SELECT DISTINCT a.sys_id, a.sub_id, a.mod_id FROM Cte2