CREATE TABLE test(id int, aa int, bb int, cc varchar(2)) GO INSERT INTO test SELECT 1,1,NULL,'a' UNION ALL SELECT 2,2,NULL,'a' UNION ALL SELECT 3,3,NULL,'a' UNION ALL SELECT 4,4,NULL,'c' UNION ALL SELECT 5,5,NULL,'c' UNION ALL SELECT 6,4,NULL,'ds' UNION ALL SELECT 7,3,NULL,'ad' GO WITH t AS( SELECT cc, ROW_NUMBER() OVER (ORDER BY MIN(id)) n FROM test GROUP BY cc ) UPDATE test SET bb=t.n+1000 FROM test,t WHERE t.cc = test.ccSELECT * FROM test id aa bb cc ----------- ----------- ----------- ---- 1 1 1001 a 2 2 1001 a 3 3 1001 a 4 4 1002 c 5 5 1002 c 6 4 1003 ds 7 3 1004 ad
1 1 1001 a 2 2 1001 a 3 3 1001 a 4 4 1002 c 5 5 1002 c 6 4 1003 ds 7 3 1004 ad 有个问题如果在家一行。 8 3 null a 这一列 的BB应该是递增为1005 还是和为1001了.
GO
INSERT INTO test
SELECT 1,1,NULL,'a' UNION ALL
SELECT 2,2,NULL,'a' UNION ALL
SELECT 3,3,NULL,'a' UNION ALL
SELECT 4,4,NULL,'c' UNION ALL
SELECT 5,5,NULL,'c' UNION ALL
SELECT 6,4,NULL,'ds' UNION ALL
SELECT 7,3,NULL,'ad'
GO
WITH t AS(
SELECT cc,
ROW_NUMBER() OVER (ORDER BY MIN(id)) n
FROM test
GROUP BY cc
)
UPDATE test
SET bb=t.n+1000
FROM test,t
WHERE t.cc = test.ccSELECT * FROM test
id aa bb cc
----------- ----------- ----------- ----
1 1 1001 a
2 2 1001 a
3 3 1001 a
4 4 1002 c
5 5 1002 c
6 4 1003 ds
7 3 1004 ad
2 2 1001 a
3 3 1001 a
4 4 1002 c
5 5 1002 c
6 4 1003 ds
7 3 1004 ad
有个问题如果在家一行。
8 3 null a 这一列 的BB应该是递增为1005 还是和为1001了.