递归查询所有父子节点数据

2018-09-18 12:17:05 1.4k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

中科蓝讯AB536X/AB892X芯片开发避坑指南：Downloader配置中那些容易忽略的细节本文详细解析中科蓝讯AB536X/AB892X芯片开发中Downloader配置的关键细节，包括软开关机实现、触摸灵敏度调试、量产校频等常见问题。特别针对无PR口芯片的替代方案、触摸参数优化、校频流程等易忽略环节提供实用解决方案，帮助开发者规避量产风险，提升开发效率。阅读详情



CREATE TABLE D_DeviceList  

( 

ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

FID int,

FacilityCode NVARCHAR(100) NOT NULL,

FacilityName NVARCHAR(100) NOT NULL,

)



 INSERT INTO dbo.D_DeviceList

( FID ,FacilityCode ,FacilityName)VALUES  ( 0 ,'设备001','设备001')

INSERT INTO dbo.D_DeviceList

( FID ,FacilityCode ,FacilityName)VALUES  ( 1 ,'设备002','设备002')

INSERT INTO dbo.D_DeviceList

( FID ,FacilityCode ,FacilityName)VALUES  ( 1 ,'设备003','设备003')

INSERT INTO dbo.D_DeviceList

( FID ,FacilityCode ,FacilityName)VALUES  ( 0 ,'设备004','设备004')

INSERT INTO dbo.D_DeviceList

( FID ,FacilityCode ,FacilityName)VALUES  ( 3 ,'设备004','设备004')

INSERT INTO dbo.D_DeviceList

( FID ,FacilityCode ,FacilityName)VALUES  ( 2 ,'设备004','设备004')





//这样查询可以查询出指定的父级节点下，包含父节点所有子节点的数据。



with temp_users as

(

select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList  WHERE ID=1

union all

select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID

)

select * from temp_users

如果需要查询所有的父子节点数据，而不是只查询某一个父节点的数据，该怎么做？

...全文

Ansys Zemax与Speos协同设计：从HUD光学优化到驾驶环境可视化全流程解析本文深入解析了如何利用Ansys Zemax与Speos进行协同设计，实现从HUD光学系统核心优化到驾驶环境可视化验证的全流程。文章强调了HUD设计是光学性能与视觉感知的深度耦合，通过Zemax进行精密光学设计，再利用Speos进行系统级仿真与感知模拟，最终完成从虚拟到现实的闭环验证，有效提升HUD产品的开发效率与可靠性。阅读详情

细嗅蔷薇

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

二月十六 2018-09-27

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([ID] int,[FID] int,[FacilityCode] nvarchar(25),[FacilityName] nvarchar(25),[level] int)

Insert #T

select 1,0,N'设备001',N'设备001',1 union all

select 2,1,N'设备002',N'设备002',2 union all

select 3,1,N'设备003',N'设备003',2 union all

select 5,3,N'设备005',N'设备005',3 union all

select 6,2,N'设备006',N'设备006',3 union all

select 4,0,N'设备004',N'设备004',1

Go

--测试数据结束

;WITH cte AS (

SELECT * FROM #T WHERE FacilityCode='设备005'

UNION ALL

SELECT #T.* FROM #T JOIN cte ON cte.FID=#t.ID

)

SELECT * FROM cte ORDER BY cte.level

RINK_1 2018-09-26

打赏
举报

引用 8 楼 lovesheng1212 的回复:

[quote=引用 5 楼 lovesheng1212 的回复:] [quote=引用 2 楼 RINK_1 的回复:]



with temp_users as
(
select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList A
WHERE not exists (select 1 from D_DeviceList  where ID=A.FID)
union all
select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID
)
select * from temp_users

数据结果对的，level层级不对。[/quote] 看错了，level层级是对的[/quote] #14的不行？

细嗅蔷薇 2018-09-26

打赏
举报

引用 13 楼 sinat_28984567 的回复:

[quote=引用 12 楼 lovesheng1212 的回复:] [quote=引用 11 楼 sinat_28984567 的回复:] 这样可以把自己所有子集放到自己下边


;WITH    t AS ( SELECT   * ,
                        CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort,
						1 AS level
               FROM     D_DeviceList
               WHERE    FID = 0
               UNION ALL
               SELECT   D_DeviceList.* ,
                        CAST(sort + RIGHT('000' + CAST(D_DeviceList.[Id] AS VARCHAR),
                                          3) AS VARCHAR(MAX)),
										  level+1
               FROM     t
                        INNER JOIN D_DeviceList ON t.Id = D_DeviceList.FID
             )
SELECT ID,FID,FacilityCode,FacilityName,t.level FROM t ORDER BY sort

有个问题，就是我查询的时候，如果我查询的是结果是子节点，可以不可以把他的所有父节点都带出来？比如：我查询设备004，设备001、设备002 都会出来，因为他们是设备004 的上级[/quote] 可以，把测试搜索的数据和想要的对应结果写一下[/quote] 结果的层级还是，父级的下一行数据是自己的子数据。 ID FID FacilityCode FacilityName level 1 0 设备001 设备001 1 2 1 设备002 设备002 2 3 1 设备003 设备003 2 5 3 设备005 设备005 3 6 2 设备006 设备006 3 4 0 设备004 设备004 1 这种。查询的子节点会显示所有的上级：比如：我查询设备005，设备003、设备001 都会出来，因为他们是设备005 的上级 ID FID FacilityCode FacilityName level 1 0 设备001 设备001 1 3 1 设备003 设备003 2 5 3 设备005 设备005 3 拜托！

RINK_1 2018-09-20

打赏
举报

以任意节点为基准，同时查询其父节点和子节点。


if object_id(N'tempdb.dbo.#D_DeviceList') is not null
drop table #D_DeviceList
go

CREATE TABLE #D_DeviceList  
( 
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
FID int,
FacilityCode NVARCHAR(100) NOT NULL,
FacilityName NVARCHAR(100) NOT NULL,
)
 
 INSERT INTO #D_DeviceList
( FID ,FacilityCode ,FacilityName)VALUES  ( 0 ,'设备001','设备001')
INSERT INTO #D_DeviceList
( FID ,FacilityCode ,FacilityName)VALUES  ( 1 ,'设备002','设备002')
INSERT INTO #D_DeviceList
( FID ,FacilityCode ,FacilityName)VALUES  ( 1 ,'设备003','设备003')
INSERT INTO #D_DeviceList
( FID ,FacilityCode ,FacilityName)VALUES  ( 0 ,'设备004','设备004')
INSERT INTO #D_DeviceList
( FID ,FacilityCode ,FacilityName)VALUES  ( 3 ,'设备004','设备004')
INSERT INTO #D_DeviceList
( FID ,FacilityCode ,FacilityName)VALUES  ( 2 ,'设备004','设备004')
INSERT INTO #D_DeviceList
( FID ,FacilityCode ,FacilityName)VALUES  ( 5 ,'设备005','设备005')



with cte
as
(select *,1 as LEVEL from #D_DeviceList where FacilityCode='设备004'
 union all
 select A.*,LEVEL-1 
 from #D_DeviceList A
 join cte as B on A.FID=B.ID
 where B.level<=1
 union all
 select A.*,LEVEL+1 
 from #D_DeviceList A
 join cte as B on A.ID=B.FID
 where B.level>=1)

select * from cte

二月十六 2018-09-20

打赏
举报

引用 12 楼 lovesheng1212 的回复:

[quote=引用 11 楼 sinat_28984567 的回复:]
这样可以把自己所有子集放到自己下边



;WITH    t AS ( SELECT   * ,

                        CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort,

						1 AS level

               FROM     D_DeviceList

               WHERE    FID = 0

               UNION ALL

               SELECT   D_DeviceList.* ,

                        CAST(sort + RIGHT('000' + CAST(D_DeviceList.[Id] AS VARCHAR),

                                          3) AS VARCHAR(MAX)),

										  level+1

               FROM     t

                        INNER JOIN D_DeviceList ON t.Id = D_DeviceList.FID

             )

SELECT ID,FID,FacilityCode,FacilityName,t.level FROM t ORDER BY sort

有个问题，就是我查询的时候，如果我查询的是结果是子节点，可以不可以把他的所有父节点都带出来？
比如：我查询设备004，设备001、设备002 都会出来，因为他们是设备004 的上级[/quote]
可以，把测试搜索的数据和想要的对应结果写一下

细嗅蔷薇 2018-09-20

打赏
举报

引用 11 楼 sinat_28984567 的回复:

这样可以把自己所有子集放到自己下边



;WITH    t AS ( SELECT   * ,

                        CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort,

						1 AS level

               FROM     D_DeviceList

               WHERE    FID = 0

               UNION ALL

               SELECT   D_DeviceList.* ,

                        CAST(sort + RIGHT('000' + CAST(D_DeviceList.[Id] AS VARCHAR),

                                          3) AS VARCHAR(MAX)),

										  level+1

               FROM     t

                        INNER JOIN D_DeviceList ON t.Id = D_DeviceList.FID

             )

SELECT ID,FID,FacilityCode,FacilityName,t.level FROM t ORDER BY sort

有个问题，就是我查询的时候，如果我查询的是结果是子节点，可以不可以把他的所有父节点都带出来？
比如：我查询设备004，设备001、设备002 都会出来，因为他们是设备004 的上级

细嗅蔷薇 2018-09-19

打赏
举报

引用 6 楼 sinat_28984567 的回复:

[quote=引用 4 楼 lovesheng1212 的回复:]
[quote=引用 3 楼 sinat_28984567 的回复:]
把where 条件去掉

with temp_users as

(

select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList 

union all

select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID

)

select * from temp_users

去掉where，数据会有重复的。[/quote]
可以加disdinct去重，
还得得看楼主想要什么数据，结果什么样的说一下
[/quote]
数据结果想要是：
ID FID FacilityCode FacilityName level
1 0 设备001 设备001 1
2 1 设备002 设备002 2
3 1 设备003 设备003 2
5 3 设备004 设备004 3
6 2 设备004 设备004 3
4 0 设备004 设备004 1

父级的下一行数据是自己的子数据。

细嗅蔷薇 2018-09-19

打赏
举报

引用 1 楼 wmxcn2000 的回复:

结果是什么样子的，也说一下。

数据结果想要是：
ID FID FacilityCode FacilityName level
1 0 设备001 设备001 1
2 1 设备002 设备002 2
3 1 设备003 设备003 2
5 3 设备004 设备004 3
6 2 设备004 设备004 3
4 0 设备004 设备004 1

父级的下一行数据是自己的子数据。

二月十六 2018-09-19

打赏
举报

引用 4 楼 lovesheng1212 的回复:

[quote=引用 3 楼 sinat_28984567 的回复:]
把where 条件去掉

with temp_users as

(

select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList 

union all

select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID

)

select * from temp_users

去掉where，数据会有重复的。[/quote]
可以加disdinct去重，
还得得看楼主想要什么数据，结果什么样的说一下

细嗅蔷薇 2018-09-19

打赏
举报

引用 5 楼 lovesheng1212 的回复:

[quote=引用 2 楼 RINK_1 的回复:]





with temp_users as

(

select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList A

WHERE not exists (select 1 from D_DeviceList  where ID=A.FID)

union all

select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID

)

select * from temp_users

数据结果对的，level层级不对。[/quote]
看错了，level层级是对的

细嗅蔷薇 2018-09-19

打赏
举报

引用 2 楼 RINK_1 的回复:





with temp_users as

(

select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList A

WHERE not exists (select 1 from D_DeviceList  where ID=A.FID)

union all

select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID

)

select * from temp_users

数据结果对的，level层级不对。

细嗅蔷薇 2018-09-19

打赏
举报

引用 3 楼 sinat_28984567 的回复:

把where 条件去掉

with temp_users as

(

select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList 

union all

select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID

)

select * from temp_users

去掉where，数据会有重复的。

二月十六 2018-09-19

打赏
举报

这样可以把自己所有子集放到自己下边



;WITH    t AS ( SELECT   * ,

                        CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort,

						1 AS level

               FROM     D_DeviceList

               WHERE    FID = 0

               UNION ALL

               SELECT   D_DeviceList.* ,

                        CAST(sort + RIGHT('000' + CAST(D_DeviceList.[Id] AS VARCHAR),

                                          3) AS VARCHAR(MAX)),

										  level+1

               FROM     t

                        INNER JOIN D_DeviceList ON t.Id = D_DeviceList.FID

             )

SELECT ID,FID,FacilityCode,FacilityName,t.level FROM t ORDER BY sort

二月十六 2018-09-19

打赏
举报

父级下是子集，那第二级和第三级呢？
ID是2下边为什么不是6，为什么是3？对第二级有特殊要求？

二月十六 2018-09-18

打赏
举报

把where 条件去掉

with temp_users as

(

select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList 

union all

select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID

)

select * from temp_users

RINK_1 2018-09-18

打赏
举报



with temp_users as
(
select ID,FID,FacilityCode,FacilityName,cast(1 as int) as [level] from D_DeviceList A
WHERE not exists (select 1 from D_DeviceList  where ID=A.FID)
union all
select  A.ID,A.FID,A.FacilityCode,A.FacilityName,b.[level]+1 as [level] from D_DeviceList A,temp_users B where A.FID=B.ID
)
select * from temp_users

卖水果的net 版主 2018-09-18

打赏
举报

结果是什么样子的，也说一下。

递归查询所有父子节点数据

相关推荐