日期:2014-05-17 浏览次数:20587 次
--CREATE TABLE 原表(NAME VARCHAR(10))
--INSERT INTO 原表
--SELECT 'a'
--UNION ALL
--SELECT 'a'
--UNION ALL
--SELECT 'a'
--UNION ALL
--SELECT 'b'
--UNION ALL
--SELECT 'b'
--UNION ALL
--SELECT 'c'
--UNION ALL
--SELECT 'c'
--UNION ALL
--SELECT 'c'
--UNION ALL
--SELECT 'd'
--UNION ALL
--SELECT 'e'
SELECT NAME+'-'+CONVERT(VARCHAR(10),ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY GETDATE()))
FROM 原表
/*
---------------------
a-1
a-2
a-3
b-1
b-2
c-1
c-2
c-3
d-1
e-1
*/
--sql2000
DECLARE @a TABLE(a VARCHAR(10),b INT )
INSERT @a SELECT 'a',NULL
UNION ALL SELECT 'a',NULL
UNION ALL SELECT 'a',NULL
UNION ALL SELECT 'b',NULL
UNION ALL SELECT 'b',NULL
UNION ALL SELECT 'c',NULL
UNION ALL SELECT 'c',NULL
UNION ALL SELECT 'c',NULL
UNION ALL SELECT 'd',NULL
UNION ALL SELECT 'e',NULL
DECLARE @i INT
DECLARE @t VARCHAR(20)
UPDATE @a SET b =@i,@i=CASE WHEN a=@t THEN ISNULL(@i,1)+1 ELSE 1 END ,@t=a
SELECT a,a+'-'+LTRIM(b) b FROM @a
--result
/*a b
---------- -----------------------
a a-1
a a-2
a a-3
b b-1
b b-2
c c-1
c