部门ID DEID 部门ID
父ID FID 父ID
部门名称 DEPARTMENT 部门名称
DEID FID DEPARTMENT
1 0 XXX
2 0 XXX
3 1 xxxx
4 2 xxxx
5 3 xxxxx
6 3 xxxxx
7 4 xxxxx
8 5 xxxxxx
9 8 xxxxxxxx
10 9 xxxxxxxx
11 7 xxxxx
... ... ...目前已知一个DEID 例如等于 10
需要列出所有上层的 FID和DEPARTMENT
请教如何实现
父ID FID 父ID
部门名称 DEPARTMENT 部门名称
DEID FID DEPARTMENT
1 0 XXX
2 0 XXX
3 1 xxxx
4 2 xxxx
5 3 xxxxx
6 3 xxxxx
7 4 xxxxx
8 5 xxxxxx
9 8 xxxxxxxx
10 9 xxxxxxxx
11 7 xxxxx
... ... ...目前已知一个DEID 例如等于 10
需要列出所有上层的 FID和DEPARTMENT
请教如何实现
SELECT DISTINCT A.DEID,A.FID,A.DEPARTMENT FROM TABLE A INNER JOIN TABLE B ON A.FID=B.DEID AND A.FID< 10 (给定的)
有顺序要求,如你给出的模型2.递归处理,就不用考虑其它条件
CREATE TABLE test (DEID INT ,FID INT,Departement varchar(20))
INSERT INTO test
SELECT 1,0,'aaa' UNION ALL
SELECT 2,0,'bbb' UNION ALL
SELECT 3,1,'ccc' UNION ALL
SELECT 4,2,'ddd' UNION ALL
SELECT 5,3,'eee' UNION ALL
SELECT 6,3,'fff' UNION ALL
SELECT 7,4,'ggg' UNION ALL
SELECT 8,5,'hhh' UNION ALL
SELECT 9,8,'jjj' UNION ALL
SELECT 10,9,'kkk' UNION ALL
SELECT 11,7,'lll' DECLARE @deID int
SET @deID=10declare @t table(id int,Departement VARCHAR(20))
insert @t SELECT FID,t.Departement FROM test t WHERE t.DEID = @deIDwhile @@rowcount > 0
INSERT @t SELECT A.FID, A.Departement FROM test AS A INNER JOIN @t AS B
ON A.DEID=B.id AND A .FID NOT IN (SELECT ID FROM @t )
select * from @t
GO
id Departement
----------- --------------------
9 kkk
8 jjj
5 hhh
3 eee
1 ccc
0 aaa(6 行受影响)
INSERT INTO test
SELECT 1,0,'aaa' UNION ALL
SELECT 2,0,'bbb' UNION ALL
SELECT 3,1,'ccc' UNION ALL
SELECT 4,2,'ddd' UNION ALL
SELECT 5,3,'eee' UNION ALL
SELECT 6,3,'fff' UNION ALL
SELECT 7,4,'ggg' UNION ALL
SELECT 8,5,'hhh' UNION ALL
SELECT 9,8,'jjj' UNION ALL
SELECT 10,9,'kkk' UNION ALL
SELECT 11,7,'lll' DECLARE @deID int
SET @deID=10declare @t table(id int,Departement VARCHAR(20))
insert @t SELECT FID,t.Departement FROM test t WHERE t.DEID = @deIDwhile @@rowcount > 0
INSERT @t SELECT A.FID, A.Departement FROM test AS A INNER JOIN @t AS B
ON A.DEID=B.id AND A .FID NOT IN (SELECT ID FROM @t )
select * from @t
GO
id Departement
----------- --------------------
9 kkk
8 jjj
5 hhh
3 eee
1 ccc
0 aaa(6 行受影响)