--> liangCK小梁 于2008-10-29
--> 生成测试数据: @T
DECLARE @T TABLE (a VARCHAR(2),b VARCHAR(6))
INSERT INTO @T
SELECT '01','000001' UNION ALL
SELECT '01','000002' UNION ALL
SELECT '01','000003' UNION ALL
SELECT '02','000004' UNION ALL
SELECT '02','000005' UNION ALL
SELECT '02','000006' UNION ALL
SELECT '02','000007' UNION ALL
SELECT '02','000008'--SQL查询如下:SELECT a,a+RIGHT('0000'+RTRIM(ROW_NUMBER() OVER(PARTITION BY a ORDER BY b)),4) AS b
FROM @T/*
a b
---- ----------
01 010001
01 010002
01 010003
02 020001
02 020002
02 020003
02 020004
02 020005(8 行受影响)
*/
--> 生成测试数据: @T
DECLARE @T TABLE (a VARCHAR(2),b VARCHAR(6))
INSERT INTO @T
SELECT '01','000001' UNION ALL
SELECT '01','000002' UNION ALL
SELECT '01','000003' UNION ALL
SELECT '02','000004' UNION ALL
SELECT '02','000005' UNION ALL
SELECT '02','000006' UNION ALL
SELECT '02','000007' UNION ALL
SELECT '02','000008'--SQL查询如下:SELECT a,a+RIGHT('0000'+RTRIM(ROW_NUMBER() OVER(PARTITION BY a ORDER BY b)),4) AS b
FROM @T/*
a b
---- ----------
01 010001
01 010002
01 010003
02 020001
02 020002
02 020003
02 020004
02 020005(8 行受影响)
*/
INSERT INTO @T
SELECT '01','000001' UNION ALL
SELECT '01','000002' UNION ALL
SELECT '01','000003' UNION ALL
SELECT '02','000004' UNION ALL
SELECT '02','000005' UNION ALL
SELECT '02','000006' UNION ALL
SELECT '02','000007' UNION ALL
SELECT '02','000008'
select a,a+substring(b,3,len(b)) b from @T/*
a b
---- --------
01 010001
01 010002
01 010003
02 020004
02 020005
02 020006
02 020007
02 020008
*/
INSERT INTO @T
SELECT '01','000001' UNION ALL
SELECT '01','000002' UNION ALL
SELECT '01','000003' UNION ALL
SELECT '02','000004' UNION ALL
SELECT '02','000005' UNION ALL
SELECT '02','000006' UNION ALL
SELECT '02','000007' UNION ALL
SELECT '02','000008'
select a , a + right('0000' + cast(px as varchar),4) b from
(
select a,b,px=(select count(1) from @t where a = t.a and b<t.b) + 1 from @T t
) m/*
a b
---- ----------
01 010001
01 010002
01 010003
02 020001
02 020002
02 020003
02 020004
02 020005(所影响的行数为 8 行)
*/
DECLARE @T TABLE (a VARCHAR(2),b VARCHAR(6))
INSERT INTO @T
SELECT '01','000001' UNION ALL
SELECT '01','000002' UNION ALL
SELECT '01','000003' UNION ALL
SELECT '02','000004' UNION ALL
SELECT '02','000005' UNION ALL
SELECT '02','000006' UNION ALL
SELECT '02','000007' UNION ALL
SELECT '02','000008'select a,a+substring(b,len(a)+1,len(b)-len(a)) as b from @t
/*
a b
01 010001
01 010002
01 010003
02 020004
02 020005
02 020006
02 020007
02 020008
*/