表A
id????name?????parentid
1?????A??????? B
2?????B???? C
3?????C??????? D
4?????Z????? X
5?????X????? V
6?????V??? N
我想得出如下一张表
id????name?????parentid rootid
1?????A??????? B D
2?????B???? C D
3?????C??????? D D
4?????Z????? X N
5?????X????? V N
6?????V??? N N
------解决思路----------------------
create table T
(
id int,
A varchar(10),
B Varchar(10)
)
insert into t values(1, 'A', 'B')
insert into t values(2, 'B', 'C')
insert into t values(3, 'C', 'D')
insert into t values(4, 'Z', 'X')
insert into t values(5, 'X', 'V')
insert into t values(6, 'V', 'N')
With CT
AS
(
Select A As p,* From T
Union All
Select p ,T.* From ct Inner Join T on Ct.B=T.A
)
Select *,(Select B From CT Where P=T.A And
id=(Select MAX(id) From CT Where p=T.A)) As P
From T
------解决思路----------------------
IF NOT OBJECT_ID('[t]') IS NULL
DROP TABLE [t]
GO
CREATE TABLE [t]([id] INT,[name] VARCHAR(2),[parentid] VARCHAR(2))
INSERT [t]
SELECT 1,'A','B' UNION ALL
SELECT 2,'B','C' UNION ALL
SELECT 3,'C','D' UNION ALL
SELECT 4,'Z','X' UNION ALL
SELECT 5,'X','V' UNION ALL
SELECT 6,'V','N'
GO
--SELECT * FROM [A]
-->SQL查询如下:
;with ct as
(
select *,rootid=parentid from t where parentid NOT IN (SELECT NAME FROM t)
union all
select t.*,ct.rootid from t JOIN ct on t.parentid=ct.NAME
)
select * from ct order by id
/*
id name parentid rootid
----------- ---- -------- ------
1 A B D
2 B C D
3 C D D
4 Z X N
5 X V N
6 V N N
(6 行受影响)
*/