select A01 from a where A01 in (select B01 from a where A01 in (select B01 from a where a0190='70810001') ) SQL select 实用 [解决办法] 大体应该是这个意思:
select a.本人,b.直接上级 from Employees a left join Employees b on a.直接上级 = b.本人
[解决办法] 要是update语句的话可以这样 update t1 set t1.c1=@value where PK(主键列) in (select 外键列明 from .....)这样可以多个记录同时更新,不知道理解的对不 ------解决方案--------------------
Drop table #tb; Create Table #tb(本人 varchar(10),直接上级 varchar(10),间接上级 varchar(10))
Insert Into #Tb Select 'A01' as 本人, 'B01' as 直接上级,'' as 间接上级 Union ALl Select 'B01' ,'C01','' Union ALl Select 'C01' , 'D01' ,''
Update t1 Set t1.间接上级 = t2.直接上级 From #tb as t1 Left Join #tb as t2 On t1.直接上级 = t2.本人
Select * From #tb Where 间接上级 is Not Null /* A01B01C01 B01C01D01 */
[解决办法]
declare @EmployeE tabLe(本人 varchar(10), 上级 varchar(10)) insert inTo @Employee(本人, 上级) values('A01', 'B01') insert into @Employee(本人, 上级) values('B01', 'C01') insert into @Employee(本人, 上级) values('C01', 'D01'); insert into @Employee(本人, 上级) values('A02', 'B02'); insert into @Employee(本人, 上级) values('B02', 'C02'); insert into @Employee(本人, 上级) values('C02', 'D02');
declare @Emp table(rowno int, 本人 varchar(10),上级 varchar(10)) insert into @Emp select rowno= row_number() over(order by (select 1)),* from @EmployeE
;WitH R(本人标记, rowno, 本人, 上级) AS( select 1 AS 本人标记, rowno, 本人, 上级 fROM @Emp --WHERE 本人='A01' UNION ALL SELECT 0 AS 本人标记, R.rowno, E.本人, E.上级 FROM R INNER JOIN @Emp AS e ON R.上级 = E.本人 ) SELECT 本人,上级, 间接上级 = replace(replace( (SELECT u=上级 from R as r2 where r2.rowno=r.rowno for xml auto) , '<r2 u="',''),'"/>',',') FROM R WHERE 本人标记 = 1