请教！SQL中递归问题。已写好测试数据和表

Question

--测试数据
create table category 
(
id int identity(1,1),
parentid int not null,
domain nvarchar(200)
)
go
insert into category(parentid,domain) values(0,'www.a.com'),(1,''),(2,'www.b.com'),(3,''),(4,'') ,(5,'')
go
create table content 
(
id int identity(1,1),
category int not null
)
go
insert into content(category) values(1),(2),(3),(4),(5),(6)
--数据内容
表category
id father domain
1  0   www.a.com
2  1   null
3  2   www.b.com
4  3   null
5  0      www.c.com
6  5   www.d.com
表content,其中category 就是表category的id
id category 
1  1   
2  2   
3  3   
4  4   
5  5      
6  6   
想得到两个结果
1.表category
id father domain url
1  0   www.a.com www.a.com
2  1   null www.a.com/2
3  2   www.b.com www.b.com
4  3   null www.b.com/4
5  4       null www.b.com/4/5
6  5   null  www.b.com/4/5/6
2.
id content url 
1  1    www.a.com/1
2  2    www.a.com/2/2
3  3    www.b.com/3
4  4    www.b.com/4/4
5  5    www.b.com/4/5/5
6  6    www.b.com/4/5/6/6

Answer 1

--参考一下实例
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
 DROP TABLE [tb]
GO
CREATE TABLE [tb](GUID INT IDENTITY,[col1] NVARCHAR(10),[col2] NVARCHAR(20))
INSERT [tb]
SELECT N'A','01' UNION ALL
SELECT N'B','01.01' UNION ALL
SELECT N'C','01.01.01' UNION ALL
SELECT N'F','01.01.01.01' UNION ALL
SELECT N'E','01.01.01.02' UNION ALL
SELECT N'D','01.01.01.03' UNION ALL
SELECT N'O','02' UNION ALL
SELECT N'P','02.01' UNION ALL
SELECT N'Q','02.01.01' 
GO
--SELECT * FROM [tb]-->SQL查询如下:---另一种方法
;WITH T AS
(
   SELECT *,PATH=CAST([COL1] AS VARCHAR(1000)) FROM TB A
       WHERE NOT EXISTS(
        SELECT 1 FROM TB 
     WHERE A.COL2 LIKE COL2+'%' 
   AND LEN(A.COL2)>LEN(COL2))
   UNION ALL
   SELECT A.*,CAST(PATH+'-->'+A.COL1 AS VARCHAR(1000))
   FROM TB A 
   JOIN T B 
        ON A.COL2 LIKE B.COL2+'%'            AND LEN(A.COL2)-3=LEN(B.COL2)
)SELECT * FROM T ORDER BY LEFT(COL2,2)/*GUID        COL1        COL2                  PATH----------- ---------- -------------------- --------------------1           A          01                   A2           B          01.01                A-->B3           C          01.01.01             A-->B-->C4           F          01.01.01.01          A-->B-->C-->F5           E          01.01.01.02          A-->B-->C-->E6           D          01.01.01.03          A-->B-->C-->D7           O          02                   O8           P          02.01                O-->P9           Q          02.01.01             O-->P-->Q
(9 行受影响)*/
;WITH T AS(
    SELECT *,CAST(COL1  AS VARCHAR(1000)) AS PATH
    FROM  TB 
    WHERE COL2 NOT LIKE '%.%'
    UNION ALL
    SELECT A.*,CAST(B.PATH+'-->'+A.COL1 AS VARCHAR(1000))
    FROM TB A,T B
    WHERE A.COL2 LIKE B.COL2+'.[01-99][01-99]'
)SELECT * FROM T 
ORDER BY LEFT(COL2,2)/*GUID        COL1        COL2                  PATH----------- ---------- -------------------- --------------------1           A          01                   A2           B          01.01                A-->B3           C          01.01.01             A-->B-->C4           F          01.01.01.01          A-->B-->C-->F5           E          01.01.01.02          A-->B-->C-->E6           D          01.01.01.03          A-->B-->C-->D7           O          02                   O8           P          02.01                O-->P9           Q          02.01.01             O-->P-->Q (9 行受影响)*/

Answer 2

我看了下你的情况跟我的不太一样。我是父节点的domain列为空的话 就取上级或上上级的domain值

Answer 3

create table category 
(
id int identity(1,1),    
parentid int not null,
domain nvarchar(200)
)
go
insert into category(parentid,domain) values(0,'www.a.com'),(1,''),(2,'www.b.com'),(3,''),(4,'')    ,(5,'')
go    
create table content 
(
id int identity(1,1),    
category int not null
)
go
insert into content(category) values(1),(2),(3),(4),(5),(6)select * from categorywith cte as
(
  select *,domain as url from category where parentid=0
  union all
  select a.*,cast(((case when b.domain='' then b.url else b.domain end)+'/'+RTRIM(a.id)) as nvarchar(200)) 
     from category a join cte b on a.parentid=b.id
)select * from cte/*
id          parentid    domain        url
----------- ----------- ------------- ----------------
1           0           www.a.com     www.a.com
2           1                         www.a.com/2
3           2           www.b.com     www.a.com/2/3
4           3                         www.b.com/4
5           4                         www.b.com/4/5
6           5                         www.b.com/4/5/6(6 行受影响)                                          

Answer 4

create table category 
(
id int identity(1,1),    
parentid int not null,
domain nvarchar(200)
)
go
insert into category(parentid,domain) values(0,'www.a.com'),(1,''),(2,'www.b.com'),(3,''),(4,'')    ,(5,'')
go    
create table content 
(
id int identity(1,1),    
category int not null
)
go
insert into content(category) values(1),(2),(3),(4),(5),(6)select * from categorywith cte as
(
  select *,domain as url from category where parentid=0
  union all
  select a.*,cast(((case when b.domain='' then b.url else b.domain end)+'/'+RTRIM(a.id)) as nvarchar(200)) 
     from category a join cte b on a.parentid=b.id
)select b.*,url=a.url+'/'+rtrim(b.id) from cte a join content b on a.id=b.id/*
id          category    url
----------- ----------- -----------------
1           1           www.a.com/1
2           2           www.a.com/2/2
3           3           www.a.com/2/3/3
4           4           www.b.com/4/4
5           5           www.b.com/4/5/5
6           6           www.b.com/4/5/6/6