参考:-- 例:
CREATE TABLE TEST(ID INT IDENTITY(1,1),ZH CHAR(2),ZXH INT)
INSERT TEST(ZH,ZXH)
SELECT '01',NULL UNION ALL
SELECT '01',NULL UNION ALL
SELECT '01',NULL UNION ALL
SELECT '01',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '03',NULL UNION ALL
SELECT '03',NULL
--SELECT * FROM TESTBEGIN TRAN
UPDATE TEST SET ZXH=(Select Count(*) From TEST Where ZH=A.ZH AND ID<=A.ID) FROM TEST A
SELECT * FROM TEST
ROLLBACK TRANDROP TABLE TEST
CREATE TABLE TEST(ID INT IDENTITY(1,1),ZH CHAR(2),ZXH INT)
INSERT TEST(ZH,ZXH)
SELECT '01',NULL UNION ALL
SELECT '01',NULL UNION ALL
SELECT '01',NULL UNION ALL
SELECT '01',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '02',NULL UNION ALL
SELECT '03',NULL UNION ALL
SELECT '03',NULL
--SELECT * FROM TESTBEGIN TRAN
UPDATE TEST SET ZXH=(Select Count(*) From TEST Where ZH=A.ZH AND ID<=A.ID) FROM TEST A
SELECT * FROM TEST
ROLLBACK TRANDROP TABLE TEST
--如果沒有不重復數據的列
--只有借用臨時表
Select ID=Identity(Int,1,1),* Into #T From TableName
Select * From #T