SQL Server中的上下层(父子层)的查询

ChinaITOldMan 2018-12-28 02:57:35

有表tb的数据如下，ParentID为父层ID、ChildID为子ID，其上下层的层数没有限制，通过SQL语句写出其层级关系，用递归与不用递归怎样写，有几种写法，谢谢！

ParentID ChildID
10 31
31 81
20 22
30 50
50 51
55 58
51 62
62 90
58 67

查询后的结果为：
Level1 Level2 Level3 Level4 Level5 Level6 Level7 …..
10 31 81
20 22
30 50 51 62 90
55 58 67

...全文

715 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

ChinaITOldMan 2019-01-02

打赏
举报

引用 8 楼二月十六的回复:

这样？

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([SPID] int,[Blocked] int)
Insert #T
select 157,106 union all
select 311,152 union all
select 262,154 union all
select 85,266 union all
select 106,266 union all
select 154,266 union all
select 340,266 union all
select 348,266 union all
select 175,311 union all
select 355,311 union all
select 391,311 union all
select 380,340 union all
select 152,348 union all
select 188,190
Go
--测试数据结束
SELECT DISTINCT
    a.Blocked
FROM #T a
WHERE NOT EXISTS
(
    SELECT * FROM #T b WHERE a.Blocked = b.SPID
);

新年好，谢谢您耐心的指点！

xiaoxiangqing 2018-12-29

打赏
举报

不用递归就用cte

二月十六 2018-12-29

打赏
举报

这样？

--测试数据

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

	drop table #T

Go

Create table #T([SPID] int,[Blocked] int)

Insert #T

select 157,106 union all

select 311,152 union all

select 262,154 union all

select 85,266 union all

select 106,266 union all

select 154,266 union all

select 340,266 union all

select 348,266 union all

select 175,311 union all

select 355,311 union all

select 391,311 union all

select 380,340 union all

select 152,348 union all

select 188,190

Go

--测试数据结束

SELECT DISTINCT

    a.Blocked

FROM #T a

WHERE NOT EXISTS

(

    SELECT * FROM #T b WHERE a.Blocked = b.SPID

);

ChinaITOldMan 2018-12-28

打赏
举报

引用 5 楼二月十六的回复:

[quote=引用 3 楼 mcxhh2005 的回复：][quote=引用 2 楼二月十六的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ParentID] int,[ChildID] int)
Insert #T
select 10,31 union all
select 31,81 union all
select 20,22 union all
select 30,50 union all
select 50,51 union all
select 55,58 union all
select 51,62 union all
select 62,90 union all
select 58,67
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)
UNION ALL
SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID
),ctea as (
SELECT * FROM cte
UNION ALL
SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1
)
select oid'
;WITH cte AS (
Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)
UNION ALL
SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID
),ctea as (
SELECT * FROM cte
UNION ALL
SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1
)

SELECT  @sql = @sql + ',max(case level when ''' + RTRIM(a.level)
        + ''' then ParentID else 0 end)[level' + RTRIM(a.level) + ']'
FROM    ( SELECT DISTINCT
                    ctea.level
          FROM      ctea
        ) a
SET @sql = @sql
    + ' from ctea group by oid'
EXEC(@sql)

谢谢您的解答，不要意思还要麻烦下您， select SPID,blocked from sys.sysprocesses where blocked<>0 这个语句是得到SQL Server的阻塞数据， Blocked是引起SPID阻塞的语句，现在有以下例子。 SPID Blocked 157 106 311 152 262 154 85 266 106 266 154 266 340 266 348 266 175 311 355 311 391 311 380 340 152 348 188 190 现在要找出到底是哪一个SPID或哪几个SPID导致的Blocking，请问怎样写比较好，谢谢您！这一个和上面的例子的关系是不同的，[/quote]这个测试数据对应的结果是什么样的？[/quote] 最终的结果是SPID 266与190，因为是根本原因是这两个SPID阻塞导致的， 如：175 311 152 348 266阻塞了348，348阻塞了152，152阻塞了311，311阻塞了175，所以是266导致了最终的阻塞。另外一个是190导致的。

二月十六 2018-12-28

打赏
举报

引用 3 楼 mcxhh2005 的回复：

[quote=引用 2 楼二月十六的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ParentID] int,[ChildID] int)
Insert #T
select 10,31 union all
select 31,81 union all
select 20,22 union all
select 30,50 union all
select 50,51 union all
select 55,58 union all
select 51,62 union all
select 62,90 union all
select 58,67
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)
UNION ALL
SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID
),ctea as (
SELECT * FROM cte
UNION ALL
SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1
)
select oid'
;WITH cte AS (
Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)
UNION ALL
SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID
),ctea as (
SELECT * FROM cte
UNION ALL
SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1
)

SELECT  @sql = @sql + ',max(case level when ''' + RTRIM(a.level)
        + ''' then ParentID else 0 end)[level' + RTRIM(a.level) + ']'
FROM    ( SELECT DISTINCT
                    ctea.level
          FROM      ctea
        ) a
SET @sql = @sql
    + ' from ctea group by oid'
EXEC(@sql)

吉普赛的歌 2018-12-28

打赏
举报

USE tempdb

GO

IF OBJECT_ID('dbo.[t]') IS NOT NULL 

	DROP TABLE dbo.[t]

GO

CREATE TABLE dbo.[t](

[SPID] INT

,[Blocked] INT	

)

GO

SET NOCOUNT ON

INSERT INTO dbo.[t] VALUES(N'157',N'106')

INSERT INTO dbo.[t] VALUES(N'311',N'152')

INSERT INTO dbo.[t] VALUES(N'262',N'154')

INSERT INTO dbo.[t] VALUES(N'85',N'266')

INSERT INTO dbo.[t] VALUES(N'106',N'266')

INSERT INTO dbo.[t] VALUES(N'154',N'266')

INSERT INTO dbo.[t] VALUES(N'340',N'266')

INSERT INTO dbo.[t] VALUES(N'348',N'266')

INSERT INTO dbo.[t] VALUES(N'175',N'311')

INSERT INTO dbo.[t] VALUES(N'355',N'311')

INSERT INTO dbo.[t] VALUES(N'391',N'311')

INSERT INTO dbo.[t] VALUES(N'380',N'340')

INSERT INTO dbo.[t] VALUES(N'152',N'348')

INSERT INTO dbo.[t] VALUES(N'188',N'190')



;WITH cte AS (

SELECT * 

,CASE WHEN EXISTS(SELECT 1 FROM t AS b WHERE a.spid=b.blocked) THEN 1 ELSE 0 END AS [spidIsBlocked]

,CASE WHEN EXISTS(SELECT 1 FROM t AS b WHERE b.spid=a.blocked) THEN 1 ELSE 0 END AS [blockedBlockByOther]

FROM t AS a

)

--SELECT * FROM cte

SELECT spid

,MAX([type]) AS [level]

,(SELECT COUNT(1) FROM t WHERE t.blocked=tt.spid) AS blockedCnt 

FROM (

SELECT spid,2 AS [type] FROM cte WHERE [spidIsBlocked]=1

UNION

SELECT blocked,2 AS [type] FROM cte WHERE [blockedBlockByOther]=1

UNION

SELECT blocked,1 AS [type] FROM cte

)

AS tt

GROUP BY spid

ORDER BY blockedCnt DESC,[level] DESC

/*

level = 2 的是比较严重的，自己与其它进程相互阻塞

level = 1 的只阻塞其它进程

blockedCnt 阻塞次数

 */

/*

spid        level       blockedCnt

----------- ----------- -----------

266         1           5

311         2           3

340         2           1

348         2           1

106         2           1

152         2           1

154         2           1

190         1           1

 */

ChinaITOldMan 2018-12-28

打赏
举报

引用 2 楼二月十六的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ParentID] int,[ChildID] int)
Insert #T
select 10,31 union all
select 31,81 union all
select 20,22 union all
select 30,50 union all
select 50,51 union all
select 55,58 union all
select 51,62 union all
select 62,90 union all
select 58,67
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)
UNION ALL
SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID
),ctea as (
SELECT * FROM cte
UNION ALL
SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1
)
select oid'
;WITH cte AS (
Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)
UNION ALL
SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID
),ctea as (
SELECT * FROM cte
UNION ALL
SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1
)

SELECT  @sql = @sql + ',max(case level when ''' + RTRIM(a.level)
        + ''' then ParentID else 0 end)[level' + RTRIM(a.level) + ']'
FROM    ( SELECT DISTINCT
                    ctea.level
          FROM      ctea
        ) a
SET @sql = @sql
    + ' from ctea group by oid'
EXEC(@sql)

二月十六 2018-12-28

打赏
举报

--测试数据

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

	drop table #T

Go

Create table #T([ParentID] int,[ChildID] int)

Insert #T

select 10,31 union all

select 31,81 union all

select 20,22 union all

select 30,50 union all

select 50,51 union all

select 55,58 union all

select 51,62 union all

select 62,90 union all

select 58,67

Go

--测试数据结束

DECLARE @sql VARCHAR(MAX)

SET @sql = ';WITH cte AS (

Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)

UNION ALL

SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID

),ctea as (

SELECT * FROM cte

UNION ALL

SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1

)

select oid'

;WITH cte AS (

Select *,1 AS [level],parentid as oid from #T a WHERE NOT EXISTS(SELECT * FROM #T b WHERE a.ParentID=b.ChildID)

UNION ALL

SELECT #T.*,cte.level+1,oid FROM #T JOIN  cte ON cte.ChildID = #T.ParentID

),ctea as (

SELECT * FROM cte

UNION ALL

SELECT t.ChildID,t.ChildID,t.level+1,t.oid FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY cte.oid ORDER BY cte.ChildID DESC) rn FROM cte)t WHERE rn=1

)



SELECT  @sql = @sql + ',max(case level when ''' + RTRIM(a.level)

        + ''' then ParentID else 0 end)[level' + RTRIM(a.level) + ']'

FROM    ( SELECT DISTINCT

                    ctea.level

          FROM      ctea

        ) a

SET @sql = @sql

    + ' from ctea group by oid'

EXEC(@sql)