-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-02-10 15:10:15 -- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([a] INT,[b] INT,[c] INT) INSERT [tb] SELECT 5,245,405 UNION ALL SELECT 5,245,406 UNION ALL SELECT 5,239,396 UNION ALL SELECT 5,239,395 UNION ALL SELECT 5,240,398 UNION ALL SELECT 5,240,397 UNION ALL SELECT 5,241,399 UNION ALL SELECT 5,241,400 UNION ALL SELECT 5,242,401 UNION ALL SELECT 5,242,402 GO --SELECT * FROM [tb]-->SQL查询如下: ;with t as ( select rn=ROW_NUMBER()over(order by getdate()),* from tb ) update t set a=rnselect * from tb /* a b c 1 245 405 2 245 406 3 239 396 4 239 395 5 240 398 6 240 397 7 241 399 8 241 400 9 242 401 10 242 402 */这样?
-- Author : htl258(Tony)
-- Date : 2010-02-10 15:10:15
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT,[c] INT)
INSERT [tb]
SELECT 5,245,405 UNION ALL
SELECT 5,245,406 UNION ALL
SELECT 5,239,396 UNION ALL
SELECT 5,239,395 UNION ALL
SELECT 5,240,398 UNION ALL
SELECT 5,240,397 UNION ALL
SELECT 5,241,399 UNION ALL
SELECT 5,241,400 UNION ALL
SELECT 5,242,401 UNION ALL
SELECT 5,242,402
GO
--SELECT * FROM [tb]-->SQL查询如下:
;with t as
(
select rn=ROW_NUMBER()over(order by getdate()),*
from tb
)
update t set a=rnselect * from tb
/*
a b c
1 245 405
2 245 406
3 239 396
4 239 395
5 240 398
6 240 397
7 241 399
8 241 400
9 242 401
10 242 402
*/这样?