不很清楚需求 不知道是不是这样create table #Tmp ( ID int identity(1,1) ,A varchar(100) null ,AB varchar(100) null ,ABC varchar(100) null ,ABCD varchar(100) null ) insert into #Tmp(A) values('123') insert into #Tmp(A,AB) values('123','1234') insert into #Tmp(A,AB,ABC) values('123','1234','12345') insert into #Tmp(A,AB,ABC,ABCD) values('123','1234','12345','123456') insert into #Tmp(A) values('234') insert into #Tmp(A,AB) values('234','2345') insert into #Tmp(A,AB,ABC) values('234','2345','23456') insert into #Tmp(A,AB,ABC,ABCD) values('234','2345','23456','234567');with ttt as ( select ID,ISNULL(ABCD,ISNULL(ABC,ISNULL(AB,ISNULL(A,0))))as 'ABCD' ,case when AB IS Null then 0 else null end as 'A_PID' ,case when ABC IS Null then (select ID from #Tmp t where tt.A=t.A and t.AB IS null and tt.AB IS not null) else null end as 'AB_PID' ,case when ABCD IS Null then(select ID from #Tmp t where tt.AB=t.AB and t.ABC IS NULL and tt.ABC IS not null) --ABC_PID else (select ID from #Tmp t where tt.ABC=t.ABC and t.ABCD IS NULL) end as 'ABCD_PID' --ABCD_PID from #Tmp tt ), t2 as ( select t.ID,t.ABCD,ISNULL(t.ABCD_PID,ISNULL(t.AB_PID,ISNULL(t.A_PID,null)))as 'PID' from ttt t )
select * from t2 select * from #Tmpdrop table #Tmp
select id, case when ab is null then A when abc is null then AB when abcd is null then abc else abcd end as ABCD, case when ab is null then '' when abc is null then A when abcd is null then AB else ABC end as Parent into #tempinsert into #temp values(0, '', '')seletct a.id, a.abcd, b.id as pid from #temp a inner join #temp b on (a.parent=b.abcd)
create table #Tmp ( ID int identity(1,1) ,A varchar(100) null ,AB varchar(100) null ,ABC varchar(100) null ,ABCD varchar(100) null ) insert into #Tmp(A) values('123') insert into #Tmp(A,AB) values('123','1234') insert into #Tmp(A,AB,ABC) values('123','1234','12345') insert into #Tmp(A,AB,ABC,ABCD) values('123','1234','12345','123456') insert into #Tmp(A) values('234') insert into #Tmp(A,AB) values('234','2345') insert into #Tmp(A,AB,ABC) values('234','2345','23456') insert into #Tmp(A,AB,ABC,ABCD) values('234','2345','23456','234567')
with t as ( select id,coalesce(ABCD,ABC,AB,A) ABCD ,CASE WHEN ABCD IS NOT NULL THEN ABC WHEN ABC IS NOT NULL THEN AB WHEN AB IS NOT NULL THEN A WHEN A IS NOT NULL THEN null END as pname from #Tmp ) select a.id,a.ABCD,isnull(b.id,0) pid from t a left join t b on a.pname=b.ABCD
select *,(select PID from B where B.id = coalesce(abcd,abc,ab,a)) as PID
from A 那么如何去找到父id呢?
情况是这样的,他们有个HR系统 HR系统里面的组织机构的层级关系 就是我的表1结构 我们系统的组织机构是我的表2机构, 说白了我是要把1 转换成一个无限极的机构,因为很多原因,我放弃在代码中来完成转换,现在需要在sql里面转换
(
ID int identity(1,1)
,A varchar(100) null
,AB varchar(100) null
,ABC varchar(100) null
,ABCD varchar(100) null
)
insert into #Tmp(A) values('123')
insert into #Tmp(A,AB) values('123','1234')
insert into #Tmp(A,AB,ABC) values('123','1234','12345')
insert into #Tmp(A,AB,ABC,ABCD) values('123','1234','12345','123456')
insert into #Tmp(A) values('234')
insert into #Tmp(A,AB) values('234','2345')
insert into #Tmp(A,AB,ABC) values('234','2345','23456')
insert into #Tmp(A,AB,ABC,ABCD) values('234','2345','23456','234567');with ttt as
(
select ID,ISNULL(ABCD,ISNULL(ABC,ISNULL(AB,ISNULL(A,0))))as 'ABCD'
,case when AB IS Null then 0 else null end as 'A_PID'
,case when ABC IS Null then (select ID from #Tmp t where tt.A=t.A and t.AB IS null and tt.AB IS not null)
else null end as 'AB_PID'
,case when ABCD IS Null then(select ID from #Tmp t where tt.AB=t.AB and t.ABC IS NULL and tt.ABC IS not null) --ABC_PID
else (select ID from #Tmp t where tt.ABC=t.ABC and t.ABCD IS NULL) end as 'ABCD_PID' --ABCD_PID
from #Tmp tt ),
t2 as
(
select t.ID,t.ABCD,ISNULL(t.ABCD_PID,ISNULL(t.AB_PID,ISNULL(t.A_PID,null)))as 'PID'
from ttt t
)
select * from t2
select * from #Tmpdrop table #Tmp
case when ab is null then A
when abc is null then AB
when abcd is null then abc
else abcd end as ABCD,
case when ab is null then ''
when abc is null then A
when abcd is null then AB
else ABC end as Parent
into #tempinsert into #temp values(0, '', '')seletct a.id, a.abcd, b.id as pid
from #temp a inner join #temp b on (a.parent=b.abcd)
create table #Tmp
(
ID int identity(1,1)
,A varchar(100) null
,AB varchar(100) null
,ABC varchar(100) null
,ABCD varchar(100) null
)
insert into #Tmp(A) values('123')
insert into #Tmp(A,AB) values('123','1234')
insert into #Tmp(A,AB,ABC) values('123','1234','12345')
insert into #Tmp(A,AB,ABC,ABCD) values('123','1234','12345','123456')
insert into #Tmp(A) values('234')
insert into #Tmp(A,AB) values('234','2345')
insert into #Tmp(A,AB,ABC) values('234','2345','23456')
insert into #Tmp(A,AB,ABC,ABCD) values('234','2345','23456','234567')
with t as
(
select id,coalesce(ABCD,ABC,AB,A) ABCD
,CASE
WHEN ABCD IS NOT NULL THEN ABC
WHEN ABC IS NOT NULL THEN AB
WHEN AB IS NOT NULL THEN A
WHEN A IS NOT NULL THEN null
END as pname
from #Tmp
)
select a.id,a.ABCD,isnull(b.id,0) pid
from t a
left join t b
on a.pname=b.ABCD