oracle start with语句
create or replace view VV_Select as
select "CORPCODE","CORPDESC","USERCODE","USERDESC"
from
(
select bc.corpcode,bc.corpdesc, aa.usercode,aa.userdesc from base_corp bc,base_user aa where bc.corpcode in (select distinct(corpcode)
from base_corp t
start with t.corpcode in
(select c.corpcode
from RIGHT_RIGHTUSER A, BASE_CORP c
WHERE A.DATACODE = c.CORPCODE
AND A.CLASSFICATIONID = '1'
AND A.USERCODE = aa.usercode)
CONNECT BY PRIOR t.corpid = t.parentid)
) a
group by a.corpcode, a.corpdesc, a.usercode, a.userdesc;
SQL SERVER with 语句
create view VV_Select as
select CORPCODE,CORPDESC,USERCODE,USERDESC
from
(
SELECT BC.CORPCODE, BC.CORPDESC, AA.USERCODE, AA.USERDESC
FROM MLS_BASE_CORP BC, MLS_BASE_USER AA
WHERE BC.CORPCODE IN
(WITH STEPCTE(CORPCODE) AS
(SELECT DISTINCT (CORPCODE)
FROM MLS_BASE_CORP T
WHERE T.CORPCODE IN
(SELECT C.CORPCODE
FROM MLS_RIGHT_RIGHTUSER A,
MLS_BASE_CORP C,
MLS_BASE_USER UU
WHERE A.DATACODE = C.CORPCODE
AND A.CLASSFICATIONID = '1'
AND A.USERCODE = UU.USERCODE)
UNION ALL
SELECT A.CORPCODE
FROM MLS_BASE_CORP A
INNER JOIN STEPCTE CTE
ON CTE.CORPCODE = A.CORPCODE)
SELECT CORPCODE FROM STEPCTE
)
) a group by a.corpcode, a.corpdesc, a.usercode, a.userdesc;
创建的时候报错
[Err] 42000 - [SQL Server]关键字 'WITH' 附近有语法错误。
42000 - [SQL Server]关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句,那么前一个语句必须以分号结尾。
42000 - [SQL Server]')' 附近有语法错误。