sqlserver求大神教写树型查询

Question

把这条oracle的sql改成sqlserver的树型查询，帅哥们看过来

 SELECT
    DISTINCT nodeid,
    nodename,
    supernodeid,
    nodelevel,
    isleaf,
    disporder 
FROM
    (   SELECT
            t1.ORGNO               AS nodeid,
            t1.ORGNAME             AS nodename,
            t1.SUPERORGNO          AS supernodeid,
            t1.orglevel             AS nodelevel,
            CAST('0' AS VARCHAR(1)) AS isleaf,
            t1.disporder           AS disporder 
        FROM
            BPEUSER.pcs_org t1 
            INNER JOIN BPEUSER.sys_user t2 
            ON t1.ORGNO=t2.ORGNO 
            LEFT JOIN BPEUSER.sys_user_dep t3 
            ON t2.usercode = t3.usercode 
        WHERE
            t3.isowner=1 AND
            // 用户只能看见本机构和自己够信息
            (t1.ORGNO='
                     d833d6f19e93f0e60b959629741b60cd' OR
            t1.SUPERORGNO='d833d6f19e93f0e60b959629741b60cd') 
        UNION
SELECT
    t2.DEPNO               AS nodeid,
    t2.DEPNAME             AS nodename,
    CASE 
        WHEN t2.SUPERDEPNO='0' 
        THEN t2.orgno 
        ELSE 
        CASE 
            WHEN t2.SUPERDEPNO<>'0' 
            THEN t2.SUPERDEPNO 
        END 
    END                        AS supernodeid,
    t2.deplevel            AS nodelevel,
    CAST('0' AS VARCHAR(1)) AS isleaf,
    t2.disporder           AS disporder 
FROM
    BPEUSER.sys_user t4 
    INNER JOIN BPEUSER.sys_user_dep t3 
    ON t3.usercode = t4.usercode 
    INNER JOIN BPEUSER.SYS_DEP t2 
    ON t2.DEPNO =t3.DEPNO 
    INNER JOIN BPEUSER.pcs_org t1 
    ON t2.orgno=t1.orgno 
WHERE
    t3.isowner=1 
UNION
SELECT
    t3.userCode            AS nodeid,
    t3.username             AS nodename,
    t4.depno supernodeid,
    CAST(9 AS NUMBER(3))    AS nodelevel,
    CAST('1' AS VARCHAR(1)) AS isleaf,
    t3.disporder 
FROM
    BPEUSER.sys_user t3 
    INNER JOIN BPEUSER.sys_user_dep t4 
    ON t3.usercode = t4.usercode
    )
    START WITH nodeid IN (  SELECT
                                DISTINCT t3.userCode AS nodeid 
                            FROM
                                BPEUSER.sys_user t3 
                                INNER JOIN BPEUSER.sys_user_dep t4 
                                ON t3.usercode = t4.usercode
                                // 用户只能看见本机构人员和自己够的管理员
                            WHERE
                                t3.status='1' AND
                                t3.userCode<>'system_tjs' AND
                                t3.userCode NOT IN (SELECT
                                                        t3.userCode 
                                                    FROM
                                                        BPEUSER.sys_user t3,
                                                        BPEUSER.pcs_org t1 
                                                    WHERE
                                                        t1.orgNo=t3.orgNo AND
                                                        ((t3.orgNo=
                                                        'd833d6f19e93f0e60b959629741b60cd' 
                                                        AND
                                                        t3.isorgadmin='1') OR
                                                        (t1.superOrgNo =
                                                        'd833d6f19e93f0e60b959629741b60cd' 
                                                        AND
                                                        t3.isorgadmin<>'1'))
                                )
                                AND
                                (t3.ORGNO ='d833d6f19e93f0e60b959629741b60cd' OR
                                t3.ORGNO IN (   SELECT
                                                    t1.orgNo 
                                                FROM
                                                    BPEUSER.pcs_org t1 
                                                WHERE
                                                    t1.SUPERORGNO =
                                                    'd833d6f19e93f0e60b959629741b60cd'
                                )
                                ) 
    )
    CONNECT BY PRIOR supernodeid = nodeid 
ORDER SIBLINGS BY
    disporder

Answer 1

只不过oracle不支持主键了，用伪劣就行