;with my1 as (select * from SYS_OrgUnit where OUCode=(select OuCode from SYS_Site_Org a where a.SiteCode='201') union all select SYS_OrgUnit.* from my1,SYS_OrgUnit where my1.OUCode=SYS_OrgUnit.ParentOUCode ) , MY2 AS ( select SYS_OrgUser.* from my1 inner join SYS_OrgUser on my1.OUCode=SYS_OrgUser.OUCode inner join SYS_User on SYS_OrgUser.UserID=SYS_User.UserID ) , MY3 AS (SELECT * FROM MY2 WHERE OUCODE='00101107101' UNION ALL SELECT * FROM MY2 AS A INNER JOIN MY3 AS B ON A.OUCODE=B.PARENTOUID) SELECT * FROM MY3
你都知道部门id了,不需要部门表了吧,直接substring,like就能拿到人员了吧with my1 as (select * from SYS_OrgUnit where OUCode=(select OuCode from SYS_Site_Org a where a.SiteCode='201') union all select SYS_OrgUnit.* from my1,SYS_OrgUnit where my1.OUCode=SYS_OrgUnit.ParentOUCode ) select SYS_OrgUser.* from my1 inner join SYS_OrgUser on my1.OUCode=SYS_OrgUser.OUCode inner join SYS_User on SYS_OrgUser.UserID=SYS_User.UserID where my1.OUCode like '100101107%'
OUCode=(select OuCode from SYS_Site_Org a
where a.SiteCode='201') union all select SYS_OrgUnit.* from my1,SYS_OrgUnit where
my1.OUCode=SYS_OrgUnit.ParentOUCode ) ,
MY2 AS
(
select SYS_OrgUser.* from my1 inner join SYS_OrgUser
on my1.OUCode=SYS_OrgUser.OUCode inner join SYS_User
on SYS_OrgUser.UserID=SYS_User.UserID
)
,
MY3 AS
(SELECT * FROM MY2 WHERE OUCODE='00101107101'
UNION ALL
SELECT * FROM MY2 AS A INNER JOIN MY3 AS B ON A.OUCODE=B.PARENTOUID)
SELECT * FROM MY3
OUCode=(select OuCode from SYS_Site_Org a
where a.SiteCode='201') union all select SYS_OrgUnit.* from my1,SYS_OrgUnit where
my1.OUCode=SYS_OrgUnit.ParentOUCode )
select SYS_OrgUser.* from my1
inner join SYS_OrgUser on my1.OUCode=SYS_OrgUser.OUCode
inner join SYS_User on SYS_OrgUser.UserID=SYS_User.UserID
where my1.OUCode like '100101107%'