SQL 问题大神指点

[Juian01]

有一个树形结构，需要将superior_sid是空的记录的父级superior_sid字段更新到子级superior_sid中，如果父级superior_sid为空，则去找父级的父级的superior_sid，如果还没有继续往上找直到找见。
superior_sid PID ID
AA CC1000 CC2000
AA CC1000 CC3000
AA CC1000 CC4000
AA CC1000 CC5000
AA CC1000 CC6000
AA CC1000 CC7100
AA CC1000 CC7200
AA CC1000 CC8000
AA CC1000 CC9000
BB CC2000 CC2100
BB CC2000 CC2200
BB CC2000 CC4300
CC CC3000 CC3100
CC CC3000 CC3200
CC CC3000 CC3300
NULL CC4000 CC4100
NULL CC4000 CC4200
NULL CC5000 CC5100
NULL CC5000 CC5200
NULL CC5000 CC5300
DD CC5100 CC5101
DD CC5100 CC5102
DD CC5100 CC5103
DD CC5100 CC5104
DD CC5100 CC5105
DD CC5100 CC5106
DD CC5100 CC5107
DD CC5100 CC5108
EE CC5200 CC5201
EE CC5200 CC5202
NULL CC6000 CC6100
NULL CC6000 CC6200
NULL CC6000 CC6300
NULL CC6000 CC6400
NULL CC7100 CC7101
NULL CC7100 CC7102
FF CC7200 CC7201
FF CC7200 CC7202
FF CC7200 CC7203
FF CC7200 CC7204
FF CC7200 CC7205
FF CC7200 CC7300

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	superior_sid VARCHAR(10)   
	,PID		 VARCHAR(10)
	,ID			 VARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO t
		  select 'AA','CC1000','CC2000'
union all select 'AA','CC1000','CC3000'
union all select 'AA','CC1000','CC4000'
union all select 'AA','CC1000','CC5000'
union all select 'AA','CC1000','CC6000'
union all select 'AA','CC1000','CC7100'
union all select 'AA','CC1000','CC7200'
union all select 'AA','CC1000','CC8000'
union all select 'AA','CC1000','CC9000'
union all select 'BB','CC2000','CC2100'
union all select 'BB','CC2000','CC2200'
union all select 'BB','CC2000','CC4300'
union all select 'CC','CC3000','CC3100'
union all select 'CC','CC3000','CC3200'
union all select 'CC','CC3000','CC3300'
union all select NULL,'CC4000','CC4100'
union all select NULL,'CC4000','CC4200'
union all select NULL,'CC5000','CC5100'
union all select NULL,'CC5000','CC5200'
union all select NULL,'CC5000','CC5300'
union all select 'DD','CC5100','CC5101'
union all select 'DD','CC5100','CC5102'
union all select 'DD','CC5100','CC5103'
union all select 'DD','CC5100','CC5104'
union all select 'DD','CC5100','CC5105'
union all select 'DD','CC5100','CC5106'
union all select 'DD','CC5100','CC5107'
union all select 'DD','CC5100','CC5108'
union all select 'EE','CC5200','CC5201'
union all select 'EE','CC5200','CC5202'
union all select NULL,'CC6000','CC6100'
union all select NULL,'CC6000','CC6200'
union all select NULL,'CC6000','CC6300'
union all select NULL,'CC6000','CC6400'
union all select NULL,'CC7100','CC7101'
union all select NULL,'CC7100','CC7102'
union all select 'FF','CC7200','CC7201'
union all select 'FF','CC7200','CC7202'
union all select 'FF','CC7200','CC7203'
union all select 'FF','CC7200','CC7204'
union all select 'FF','CC7200','CC7205'
union all select 'FF','CC7200','CC7300'
GO
--1. 增加一个标量函数
IF OBJECT_ID('dbo.Fun_GetParentFirstNotNullSid') IS NOT NULL
	DROP FUNCTION dbo.Fun_GetParentFirstNotNullSid
GO
-- =============================================
-- Author:		yenange
-- Create date: 2018-08-23
-- Description:	得到 父级第一个不为NULL 的 superior_sid
-- =============================================
CREATE FUNCTION dbo.Fun_GetParentFirstNotNullSid 
(
	@id VARCHAR(10)
)
RETURNS VARCHAR(10) 
AS
BEGIN
	DECLARE @r VARCHAR(10)
	;WITH cte AS (
		SELECT *,0 AS myLevel FROM t WHERE id=@id
		UNION ALL
		SELECT a.*,b.myLevel+1 FROM t AS a INNER JOIN cte AS b ON a.id=b.pid	
	)
	SELECT TOP 1 @r=superior_sid FROM cte WHERE superior_sid IS NOT NULL ORDER BY myLevel
	RETURN @r;
END
GO

--2. 查看哪些是需要替换的：
SELECT *,dbo.Fun_GetParentFirstNotNullSid(id) AS replaceSid FROM t WHERE superior_sid IS NULL
/*
superior_sid PID        ID         replaceSid
------------ ---------- ---------- ----------
NULL         CC4000     CC4100     AA
NULL         CC4000     CC4200     AA
NULL         CC5000     CC5100     AA
NULL         CC5000     CC5200     AA
NULL         CC5000     CC5300     AA
NULL         CC6000     CC6100     AA
NULL         CC6000     CC6200     AA
NULL         CC6000     CC6300     AA
NULL         CC6000     CC6400     AA
NULL         CC7100     CC7101     AA
NULL         CC7100     CC7102     AA
*/

--3. 替换
UPDATE t 
SET superior_sid=dbo.Fun_GetParentFirstNotNullSid(id)
WHERE superior_sid IS NULL

[RINK_1]

with cte
as
(select *,ID as main_id,1 as level 
 from table where superior_sid is null
 union all
 select A.*,B.main_id,B.level+1
 from table A
 join cte B on A.ID=B.PID
 where A.superior_sid is null)
 
select A.*,B.superior_sid
from cte A 
join table B on A.PID=B.ID
where not exists (select 1 from cte where A.main_id=main_id and level>A.level)