求tsql语句：自然数连续id清零且保持原大小顺序

Question

求tsql语句：自然数连续id清零且保持原大小顺序参照表A，把表B的id列更新成表B'的样子
规则是，凡是在表A中对应sl大于20的id都在表B中清零，然后清零前大于该id的id(也就是在该id后面的所有id)都减一，以便让非零的id总保持1,2,3...的连续自然数序列。
在下面的列子中，由于在表A中，id=2和id=4对应的sl分别是21和24，所以表B中的id 2和4被清零，表B中原来的3减一变2，表B中原煤来的5减两次一变成3，无论有多少变零的表B中非零ID要保持原先的大小顺序。
这么做实际上是用把id清零的方式删除行，由于不想真正删除，才这么做。
表A
id sl
1       11
2 21
3 13
4 24
5 15表B
id ord
1       1
2 2
3 3
4 4
5 5表B'
id ord
1       1
0 2
2 3
0 4
3 5

Answer 1

DECLARE @a TABLE(id INT,sl INT)
INSERT @a SELECT 1  ,    11
UNION ALL SELECT 2 ,21
UNION ALL SELECT 3 ,13
UNION ALL SELECT 4 ,24
UNION ALL SELECT 5 ,15DECLARE @b TABLE(id INT,ord INT)
INSERT @b SELECT 1  ,    1
UNION ALL SELECT 2 ,2
UNION ALL SELECT 3 ,3
UNION ALL SELECT 4 ,4
UNION ALL SELECT 5, 5 
UPDATE b SET b.id=CASE WHEN a.sl >=20 THEN 0 ELSE b.id END
FROM @b b
INNER JOIN @a a
ON a.id=b.idSELECT * FROM @b

Answer 2

按条件置0是做到了，但是表B中的非零ID成了1,3,5，不是连续的自然数了，应该是1,2,3。

Answer 3

知道你意思了。你的sql2000还是sql2005?

Answer 4

sql2000
DECLARE @a TABLE(id INT,sl INT)
INSERT @a SELECT 1  ,    11
UNION ALL SELECT 2 ,21
UNION ALL SELECT 3 ,13
UNION ALL SELECT 4 ,24
UNION ALL SELECT 5 ,15DECLARE @b TABLE(id INT,ord INT)
INSERT @b SELECT 1  ,    1
UNION ALL SELECT 2 ,2
UNION ALL SELECT 3 ,3
UNION ALL SELECT 4 ,4
UNION ALL SELECT 5, 5 
UPDATE b SET b.id=CASE WHEN a.sl >=20 THEN 0 ELSE 
(SELECT COUNT(*) FROM @a WHERE sl>=20 AND id<b.id)+1
 END
FROM @b b
INNER JOIN @a a
ON a.id=b.idSELECT * FROM @b

Answer 5

sql2005
DECLARE @a TABLE(id INT,sl INT)
INSERT @a SELECT 1  ,    11
UNION ALL SELECT 2 ,21
UNION ALL SELECT 3 ,13
UNION ALL SELECT 4 ,24
UNION ALL SELECT 5 ,15DECLARE @b TABLE(id INT,ord INT)
INSERT @b SELECT 1  ,    1
UNION ALL SELECT 2 ,2
UNION ALL SELECT 3 ,3
UNION ALL SELECT 4 ,4
UNION ALL SELECT 5, 5 UPDATE b SET b.id=ISNULL(idx,0)
FROM @b b
LEFT JOIN
(SELECT b.id,idx= ROW_NUMBER() OVER(ORDER BY a.id)
FROM @b b
INNER JOIN @a a
ON a.id=b.id
WHERE a.sl<20
) a
ON a.id=b.idSELECT * FROM @b
/*
1 1
0 2
2 3
0 4
3 5
*/

Answer 6

差点儿就对了，但是当把表A数据改成如下就不对！
DECLARE @a TABLE(id INT,sl INT)
INSERT @a SELECT 1  ,    11
UNION ALL SELECT 2 ,21
UNION ALL SELECT 3 ,22
UNION ALL SELECT 4 ,24
UNION ALL SELECT 5 ,15
出现了以下错误结果
表B'
1 1
0 2
0 3
0 4
4 5
应该是：
1 1
0 2
0 3
0 4
2 5

Answer 7

是，sql2000下的语句，我逻辑上考滤不全。sql2005下的应该没有问题。sql2000下的我再改下。

Answer 8

2000下的符号打反了。呵sl>=20 改为 sl<20即可. 应该是统计不更改的个数，这样就对了。UPDATE b SET b.id=CASE WHEN a.sl >=20 THEN 0 ELSE 
        (SELECT COUNT(*) FROM @a WHERE sl<20 AND id<=b.id)
         END
    FROM @b b
INNER JOIN @a a
    ON a.id=b.id

Answer 9

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[sl] int)
Insert #A
select 1,11 union all
select 2,21 union all
select 3,13 union all
select 4,24 union all
select 5,15
Go
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#B') is null
drop table #B
Go
Create table #B([id] int,[ord] int)
Insert #B
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5
Go
update c
set ID=case when a.ID is not null then 0 else 
(select count(1) from #b b where not exists(select 1 from #a where ID=b.ID and sl>20) and ID<=c.ID) end
from 
#b c
left join
#a a on c.ID=a.ID and a.sl>20select * from #b
(5 行受影响)(5 行受影响)(5 行受影响)
id          ord
----------- -----------
1           1
0           2
2           3
0           4
3           5(5 行受影响)