27,579
社区成员
发帖
与我相关
我的任务
分享
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]
这个好,我看懂了,简单明了,谢谢大佬。
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
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