mysql 和sql server根据上级ID求用户管理的所有成员信息

sql server方式一：运行截图

sqlserver语句

 1 declare @account nvarchar(20)
 2 set @account = (select employee_ID from employee where employee_name='1');
 3 WITH percte AS
 4 (SELECT employee_ID, superior_ID,employee_name
 5 FROM employee
 6 WHERE employee_ID in (@account)
 7 UNION ALL
 8 SELECT c.employee_ID, c.superior_ID,c.employee_name
 9 FROM percte AS p
10 JOIN employee AS c ON c.superior_ID = p.employee_ID)
11 
12 SELECT employee_name
13 FROM percte

sql server方式二：运行截图

 

 sqlserver语句

 1    --由子级递归父项
 2 with cte(employee_ID,superior_ID,employee_name)
 3 as
 4 (--下级父项
 5 select employee_ID,superior_ID,employee_name from employee where employee_ID = 1 --子级id
 6 union all
 7 --递归结果集中的父项
 8 select t.employee_ID,t.superior_ID,t.employee_name from employee as t
 9 inner join cte as c on t.superior_ID = c.employee_ID
10 
11 )
12 select employee_ID,superior_ID,employee_name from cte order by superior_ID asc

 

mysql语句

 1 select employee_ID,employee_name from
 2 (
 3 select t1.employee_ID,t1.employee_name,
 4     if(find_in_set(superior_ID, @pids) > 0,
 5     @pids := concat(@pids, ',', employee_ID),
 6     0) as ischild
 7     from(
 8 select employee_ID,superior_ID,employee_name,department
 9 from mydatabase.employee t
10 order by superior_ID,employee_ID)t1,
11 (select @pids := (select employee_ID
12 from mydatabase.employee where employee_name='1')) t2 ) t3
13 where
14 ischild != 0
15 union 
16 select employee_ID,employee_name 
17 from mydatabase.employee where employee_name='1'

 

posted @ 2022-04-13 10:29  Octoberzbear  阅读(117)  评论(0)    收藏  举报