sqlserver查询树形结构的所有子节点

发布网友发布时间：2022-04-08 01:07

共4个回答

懂视网时间：2022-04-08 05:28

新建测试表

CREATE TABLE [dbo].[test](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[parentId] [bigint] NULL,
[name] [nvarchar](50) NULL,
[jb] [int] NULL,
)
GO

插入测试数据

INSERT INTO test ( Id, parentId, name, jb) values (1,0,‘1‘,1);
INSERT INTO test ( Id, parentId, name, jb) values (2,0,‘2‘,1);
INSERT INTO test ( Id, parentId, name, jb) values (3,1,‘11‘,2);
INSERT INTO test ( Id, parentId, name, jb) values (4,1,‘12‘,2);
INSERT INTO test ( Id, parentId, name, jb) values (5,2,‘21‘,2);
INSERT INTO test ( Id, parentId, name, jb) values (6,2,‘22‘,2);
INSERT INTO test ( Id, parentId, name, jb) values (7,3,‘111‘,3);
INSERT INTO test ( Id, parentId, name, jb) values (8,3,‘112‘,3);
INSERT INTO test ( Id, parentId, name, jb) values (9,5,‘221‘,3);
INSERT INTO test ( Id, parentId, name, jb) values (10,0,‘3‘,1);

树形结构，查询分类下所有子类

;with cte as
(
select * from test where id=1
union all
select a.* from test a join cte b on a.parentId=b.Id
)

select * from cte

sql树形结果，查询所有子类

标签：

热心网友时间：2022-04-08 02:36

用标准sql的with实现递归查询（sql2005以上肯定支持，sql2000不清楚是否支持）：

with subqry(id,name,pid) as (
select id,name,pid from test1 where id = 5
union all
select test1.id,test1.name,test1.pid from test1,subqry
where test1.pid = subqry.id
)
select * from subqry;

热心网友时间：2022-04-08 03:54

select * from tablename where pid=@pid

热心网友时间：2022-04-08 05:29

1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5
6 CREATE FUNCTION [dbo].[fn_GetSubCasNoTree](@CAS_NO VARCHAR(20))
7 RETURNS @CAS TABLE
8 (
9 CAS_NO VARCHAR(20) COLLATE DATABASE_DEFAULT,这个不加SQL2000会报错。
10 LEVEL INT
11 )
12
13 AS
14 BEGIN
15 DECLARE @LEVEL int
16 SET @LEVEL = 1
17 INSERT INTO @CAS SELECT @CAS_NO , @LEVEL
18 WHILE @@ROWCOUNT > 0
19 BEGIN
20 SET @LEVEL = @LEVEL + 1
21 INSERT INTO @CAS SELECT A.CAS_NO , @LEVEL
22 FROM CASN A , @CAS B
23 WHERE A.UP = B.CAS_NO and B.LEVEL = @LEVEL - 1
24 END
25 RETURN
26 END