树形结构数据，怎么根据他的子节点 来更新父节点。

[马猴烧酒123]

数据大概就是这个样子的。

SELECT 1 ID,0 PID,'A'NAME ,0 PCT union all
select 2,1,'B',0 union all
select 3,1,'C',0 union all
select 4,2,'B1',100 union all
select 5,2,'B2',40 union all
select 6,5,'B21',100 union all
select 7,5,'B22',100 union all
select 8,3,'C1',100 union all
select 9,3,'C2',50


ID PID 来区分上下级。
下级PCT字段， 全都是100的时候，就把上级更新成 100
最终数据我想要 这个样子 。



层级数是不固定的。这该怎么来写呢？有点迷，没有头绪。

[RINK_1]

试试看，你先SELECT出来看看结果是不是你要更新为100的数据，然后再做UPDATE操作。

CREATE TABLE #T
(ID INT,
 PID INT,
 NAME VARCHAR(10),
 PCT INT)

INSERT INTO #T
SELECT 1 ID,0 PID,'A'NAME ,0 PCT  union all
  select 2,1,'B',0 union all
  select 3,1,'C',0 union all
  select 4,2,'B1',99 union all
  select 5,2,'B2',40 union all
  select 6,5,'B21',100 union all
  select 7,5,'B22',100 union all
  select 8,3,'C1',100 union all 
  select 9,3,'C2',50 

GO

WITH CTE_1
AS
(SELECT * FROM #T A
WHERE NOT EXISTS (SELECT 1 FROM #T WHERE PID=A.ID)
AND NOT EXISTS (SELECT 1 FROM #T B 
                WHERE B.PID=A.PID AND B.PCT<>100
                AND NOT EXISTS (SELECT 1 FROM #T WHERE PID=B.ID))),


CTE_2
AS
(SELECT * FROM CTE_1
 UNION ALL
 SELECT B.* FROM CTE_2 A
 JOIN #T B ON A.PID=B.ID)

UPDATE B
SET PCT=100
FROM (SELECT DISTINCT * FROM CTE_2) AS A
JOIN #T B ON A.ID=B.ID

SELECT * FROM #T

DROP TABLE #T

[马猴烧酒123]

引用 2 楼 RINK_1 的回复:

试试看，你先SELECT出来看看结果是不是你要更新为100的数据，然后再做UPDATE操作。

CREATE TABLE #T
(ID INT,
 PID INT,
 NAME VARCHAR(10),
 PCT INT)

INSERT INTO #T
SELECT 1 ID,0 PID,'A'NAME ,0 PCT  union all
  select 2,1,'B',0 union all
  select 3,1,'C',0 union all
  select 4,2,'B1',99 union all
  select 5,2,'B2',40 union all
  select 6,5,'B21',100 union all
  select 7,5,'B22',100 union all
  select 8,3,'C1',100 union all 
  select 9,3,'C2',50 

GO

WITH CTE_1
AS
(SELECT * FROM #T A
WHERE NOT EXISTS (SELECT 1 FROM #T WHERE PID=A.ID)
AND NOT EXISTS (SELECT 1 FROM #T B 
                WHERE B.PID=A.PID AND B.PCT<>100
                AND NOT EXISTS (SELECT 1 FROM #T WHERE PID=B.ID))),


CTE_2
AS
(SELECT * FROM CTE_1
 UNION ALL
 SELECT B.* FROM CTE_2 A
 JOIN #T B ON A.PID=B.ID)

UPDATE B
SET PCT=100
FROM (SELECT DISTINCT * FROM CTE_2) AS A
JOIN #T B ON A.ID=B.ID

SELECT * FROM #T

DROP TABLE #T

这个是刚才回帖的SQL执行的结果，不对呢。 B1=99 ！=100 ，B不修改成100。 C=0 ！=100 ，A不修改成100。

[RINK_1]

引用 3 楼 马猴烧酒123 的回复:

[quote=引用 2 楼 RINK_1 的回复:] 试试看，你先SELECT出来看看结果是不是你要更新为100的数据，然后再做UPDATE操作。

CREATE TABLE #T
(ID INT,
 PID INT,
 NAME VARCHAR(10),
 PCT INT)

INSERT INTO #T
SELECT 1 ID,0 PID,'A'NAME ,0 PCT  union all
  select 2,1,'B',0 union all
  select 3,1,'C',0 union all
  select 4,2,'B1',99 union all
  select 5,2,'B2',40 union all
  select 6,5,'B21',100 union all
  select 7,5,'B22',100 union all
  select 8,3,'C1',100 union all 
  select 9,3,'C2',50 

GO

WITH CTE_1
AS
(SELECT * FROM #T A
WHERE NOT EXISTS (SELECT 1 FROM #T WHERE PID=A.ID)
AND NOT EXISTS (SELECT 1 FROM #T B 
                WHERE B.PID=A.PID AND B.PCT<>100
                AND NOT EXISTS (SELECT 1 FROM #T WHERE PID=B.ID))),


CTE_2
AS
(SELECT * FROM CTE_1
 UNION ALL
 SELECT B.* FROM CTE_2 A
 JOIN #T B ON A.PID=B.ID)

UPDATE B
SET PCT=100
FROM (SELECT DISTINCT * FROM CTE_2) AS A
JOIN #T B ON A.ID=B.ID

SELECT * FROM #T

DROP TABLE #T

这个是刚才回帖的SQL执行的结果，不对呢。 B1=99 ！=100 ，B不修改成100。 C=0 ！=100 ，A不修改成100。 [/quote] 那就反过来找不是100的上级，然后更新这些数据以外的数据。

WITH CTE_1
AS
(SELECT * FROM #T A
 WHERE NOT EXISTS (SELECT 1 FROM #T WHERE PID=A.ID)
 AND PCT<>100
 UNION ALL
 SELECT A.* 
 FROM #T A
 JOIN CTE_1 B ON A.ID=B.PID)

UPDATE #T
SET PCT=100
FROM #T
WHERE ID NOT IN
(SELECT ID FROM CTE_1)

SELECT * FROM #T

DROP TABLE #T

[马猴烧酒123]

引用 4 楼 RINK_1 的回复:

[quote=引用 3 楼 马猴烧酒123 的回复:] [quote=引用 2 楼 RINK_1 的回复:] 试试看，你先SELECT出来看看结果是不是你要更新为100的数据，然后再做UPDATE操作。

CREATE TABLE #T
(ID INT,
 PID INT,
 NAME VARCHAR(10),
 PCT INT)

INSERT INTO #T
SELECT 1 ID,0 PID,'A'NAME ,0 PCT  union all
  select 2,1,'B',0 union all
  select 3,1,'C',0 union all
  select 4,2,'B1',99 union all
  select 5,2,'B2',40 union all
  select 6,5,'B21',100 union all
  select 7,5,'B22',100 union all
  select 8,3,'C1',100 union all 
  select 9,3,'C2',50 

GO

WITH CTE_1
AS
(SELECT * FROM #T A
WHERE NOT EXISTS (SELECT 1 FROM #T WHERE PID=A.ID)
AND NOT EXISTS (SELECT 1 FROM #T B 
                WHERE B.PID=A.PID AND B.PCT<>100
                AND NOT EXISTS (SELECT 1 FROM #T WHERE PID=B.ID))),


CTE_2
AS
(SELECT * FROM CTE_1
 UNION ALL
 SELECT B.* FROM CTE_2 A
 JOIN #T B ON A.PID=B.ID)

UPDATE B
SET PCT=100
FROM (SELECT DISTINCT * FROM CTE_2) AS A
JOIN #T B ON A.ID=B.ID

SELECT * FROM #T

DROP TABLE #T

这个是刚才回帖的SQL执行的结果，不对呢。 B1=99 ！=100 ，B不修改成100。 C=0 ！=100 ，A不修改成100。 [/quote] 那就反过来找不是100的上级，然后更新这些数据以外的数据。

WITH CTE_1
AS
(SELECT * FROM #T A
 WHERE NOT EXISTS (SELECT 1 FROM #T WHERE PID=A.ID)
 AND PCT<>100
 UNION ALL
 SELECT A.* 
 FROM #T A
 JOIN CTE_1 B ON A.ID=B.PID)

UPDATE #T
SET PCT=100
FROM #T
WHERE ID NOT IN
(SELECT ID FROM CTE_1)

SELECT * FROM #T

DROP TABLE #T

[/quote] 这个好，我看懂了，简单明了，谢谢大佬。

[马猴烧酒123]

现在CSDN还有大佬帮萌新解决问题么？(这网站凉了没)