Sql Server 父子关系查询

Question

表结构如下:
CREATE TABLE tb_loc(id int, name nvarchar(50), parent int)INSERT tb_loc(id,name,parent) VALUES( 1,'河北省',NULL)INSERT tb_loc(id,name,parent) VALUES( 2,'石家庄',1)INSERT tb_loc(id,name,parent) VALUES( 3,'保定',1)INSERT tb_loc(id,name,parent) VALUES( 4,'山西省',NULL)INSERT tb_loc(id,name,parent) VALUES( 5,'太原',4)INSERT tb_loc(id,name,parent) VALUES( 6,'新华区',2)INSERT tb_loc(id,name,parent) VALUES( 7,'北焦村',6)INSERT tb_loc(id,name,parent) VALUES( 8,'大郭村',6)INSERT tb_loc(id,name,parent) VALUES( 9,'河南省',NULL)INSERT tb_loc(id,name,parent) VALUES( 10,'大郭村南',8)INSERT tb_loc(id,name,parent) VALUES( 11,'北焦村北',8)INSERT tb_loc(id,name,parent) VALUES( 12,'北焦村东',7)INSERT tb_loc(id,name,parent) VALUES( 13,'北焦村西',7)INSERT tb_loc(id,name,parent) VALUES( 14,'桥东区',3)INSERT tb_loc(id,name,parent) VALUES( 15,'桥本区',3)想要得到的结果是  ID会做为参数传入如果传进去的是 1  得到如下结果1,'河北省',NULL2,'石家庄',13,'保定',16,'新华区',214,'桥东区',315,'桥本区',37,'北焦村',68,'大郭村',612,'北焦村东',713,'北焦村西',710,'大郭村南',811,'北焦村北',8就是递归得到传入值的所有子孙值

Answer 1

declare @id int
set @id=1
;with f as
(
select * from tb where id=@id
union all
select a.* from tb a , f b where a.parent=b.id
)
select  * from f

Answer 2

with cte as(
select * from tb_loc where ID=1
union all
select a.*from tb_loc a ,cte b
where a.parent=b.id
)
select * from cte order by ISNULL(parent,0),id
id          name                                               parent
----------- -------------------------------------------------- -----------
1           河北省                                                NULL
2           石家庄                                                1
3           保定                                                 1
6           新华区                                                2
14          桥东区                                                3
15          桥本区                                                3
7           北焦村                                                6
8           大郭村                                                6
12          北焦村东                                               7
13          北焦村西                                               7
10          大郭村南                                               8
11          北焦村北                                               8(12 行受影响)

Answer 3

declare @id int
set @id=1with cte as(
select * from tb_loc where ID=@id
union all
select a.*from tb_loc a ,cte b
where a.parent=b.id
)
select * from cte order by ISNULL(parent,0),id
id          name                                               parent
----------- -------------------------------------------------- -----------
1           河北省                                                NULL
2           石家庄                                                1
3           保定                                                 1
6           新华区                                                2
14          桥东区                                                3
15          桥本区                                                3
7           北焦村                                                6
8           大郭村                                                6
12          北焦村东                                               7
13          北焦村西                                               7
10          大郭村南                                               8
11          北焦村北                                               8(12 行受影响)

Answer 4

if object_id('f_getC') is not null drop function f_getC
go
create   function   f_getC(@id   int)  
returns   @re   table(id   int,level   int)  
as  
begin  
  declare   @l   int  
  set   @l=0  
  insert   @re   select   @id,@l  
  
  while @@rowcount>0
  begin  
  set @l=@l+1
  insert @re select a.id,@l from tb_loc as a,@re as b  where b.id=a.parent and b.level=@l-1
  end
  return  
end  
go  
  select   a.*   from   tb_loc  a,f_getC(1)   b    where   a.id=b.id    order   by   b.level  
/*
id          name                                               parent      
----------- -------------------------------------------------- ----------- 
1           河北省                                                NULL
2           石家庄                                                1
3           保定                                                 1
6           新华区                                                2
14          桥东区                                                3
15          桥本区                                                3
7           北焦村                                                6
8           大郭村                                                6
12          北焦村东                                               7
13          北焦村西                                               7
10          大郭村南                                               8
11          北焦村北                                               8（所影响的行数为 12 行）*/

Answer 5

IF OBJECT_ID('[p_getbom]') IS NOT NULL
    DROP PROC p_getbom
GO
CREATE PROC p_getbom
 @id INT
AS 
WITH t AS
(
SELECT *, px = CAST(id AS VARBINARY) 
FROM tb_loc
WHERE ID = @id
UNION ALL
SELECT a.* ,CAST(px+CAST(a.id AS VARBINARY) AS VARBINARY) 
FROM tb_loc a, t b
WHERE a.parent = b.id
)
SELECT id, NAME, parent
FROM t
ORDER BY px
GOEXEC p_getbom 1
/*
id          NAME                                               parent
----------- -------------------------------------------------- -----------
1           河北省                                                NULL
2           石家庄                                                1
6           新华区                                                2
7           北焦村                                                6
12          北焦村东                                               7
13          北焦村西                                               7
8           大郭村                                                6
10          大郭村南                                               8
11          北焦村北                                               8
3           保定                                                 1
14          桥东区                                                3
15          桥本区                                                3(12 行受影响)
*/

Answer 6

IF OBJECT_ID('[p_getbom]') IS NOT NULL
    DROP PROC p_getbom
GO
CREATE PROC p_getbom
 @id INT
AS 
WITH t AS
(
    SELECT *, 0 AS lvl, px = CAST(id AS VARBINARY) 
    FROM tb_loc
    WHERE ID = @id
    UNION ALL
    SELECT a.*, lvl+1, CAST(px+CAST(a.id AS VARBINARY) AS VARBINARY) 
    FROM tb_loc a, t b
    WHERE a.parent = b.id
)
SELECT REPLICATE('.', lvl)+LTRIM(id) id, NAME, parent 
FROM t
ORDER BY px
GOEXEC p_getbom 1
/*
id         NAME                                               parent
---------- -------------------------------------------------- -----------
1          河北省                                                NULL
.2         石家庄                                                1
..6        新华区                                                2
...7       北焦村                                                6
....12     北焦村东                                               7
....13     北焦村西                                               7
...8       大郭村                                                6
....10     大郭村南                                               8
....11     北焦村北                                               8
.3         保定                                                 1
..14       桥东区                                                3
..15       桥本区                                                3(12 行受影响)
*/这样直观些

Answer 7

--sql 2005之后declare @id int
set @id=1
;with t as
(
select * from tb_loc where id=@id union all
select a.* from tb_loc a join t on a.parent=t.id
)select * from t order by isnull(parent,''),id

Answer 8

declare @id int
set @id=1
;with t as
(
select * from tb_loc where id=@id union all
select a.* from tb_loc a join t on a.parent=t.id
)
--ddd
select * from t order by isnull(parent,0),id