SQL疑难问题求解

Juian01 2018-08-22 07:35:05

有一个树形结构，需要将superior_sid是空的记录的父级superior_sid字段更新到子级superior_sid中，如果父级superior_sid为空，则去找父级的父级的superior_sid，如果还没有继续往上找直到找见。
superior_sid ID PID
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

...全文

159 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

吉普赛的歌 2018-08-23

打赏
举报

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

Juian01 2018-08-23

打赏
举报

引用 6 楼 sinat_28984567 的回复:

[quote=引用 5 楼 jujian110 的回复:]
[quote=引用 4 楼 sinat_28984567 的回复:]
EE CC5200 CC5201
EE CC5200 CC520

superior_sid 有值得不做更新，如果底层superior_sid是空就去找他的父级的superior_sid值，如果没有再去找父级的父级的superior_sid值，一直到找见[/quote]

NULL CC5000 CC5200 这条数据的pid是CC5200 吧？他去找了，找到了两条
EE CC5200 CC5201
EE CC5200 CC520

然后要把这两条中的一条的sup_sid更新到NULL CC5000 CC5200这条上吧？那用这两条中的哪一条的sup_sid更新？现在这个是两个EE一样，如果不一样呢[/quote]

ID 是父，PID是子（主键）
其实PID=CC5200的只有一条，要找他对应的SUP_SID
你上面写的这个是对应PID对应的SUP_SID,如果他已经对应了sup_sid 就不用找了
字段顺序应该为
PID(主键唯一) ID（父级） SUP_SID（要更新的字段）