求一个sql语句部门下的所有用户

[Persistence_x]

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 

如上sql可以查询出用户所在的部门信息

select * from SYS_OrgUnit;

如上sql可以查询部门表

如何查询出100101107部门下的用户也就是能查询出
OUCode='100101107101' 的用户ID xdx 这条记录

[--小F--]

;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

[xdashewan]

你都知道部门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%'