34,590
社区成员
发帖
与我相关
我的任务
分享
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
--测试数据
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
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
;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
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
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