求一SQL

Question

     各位老大,现在小弟有一表table,里面有一字段叫做地籍号,现在我要判断出里面相同的号码,把后面3位修改为按顺序排列,用什么方法比较简单一点.
     表中字段如下:
      地籍号
      5106010010030349001
     5106010010030349001
     5106010060020161000
     5106010060010170000
     5106010010020024000  
     5106010010020024000
     5106010010020024000
     5106010010020024000
     5106010010020024000
     得到的结果应该为:
     5106010010030349001
     5106010010030349002
     5106010060020161000
     5106010060010170000
     5106010010020024001
     5106010010020024002
     5106010010020024003
     5106010010020024004
     5106010010020024005
希望各位老大提供比较简单的方法..谢谢

Answer 1

DECLARE @a TABLE(a VARCHAR(100))
INSERT @a SELECT '5106010010030349001' 
union all select '5106010010030349001' 
union all select '5106010060020161000' 
union all select '5106010060010170000' 
union all select '5106010010020024000'  
union all select '5106010010020024000' 
union all select '5106010010020024000' 
union all select '5106010010020024000' 
union all select '5106010010020024000' DECLARE @b TABLE(id INT IDENTITY(1,1),b VARCHAR(100))
INSERT @b(b) SELECT * FROM @a ORDER BY aSELECT LEFT(b,LEN(b)-3)+RIGHT('00'+LTRIM((SELECT COUNT(1) FROM @b WHERE b=a.b AND id>a.id)),3)
FROM @b a ORDER BY 1--result
/*---------------------------------------
5106010010020024000
5106010010020024001
5106010010020024002
5106010010020024003
5106010010020024004
5106010010030349000
5106010010030349001
5106010060010170000
5106010060020161000（所影响的行数为 9 行）
*/

Answer 2

DECLARE @TB TABLE([地籍号] VARCHAR(19))
INSERT @TB 
SELECT '5106010010030349001' UNION ALL 
SELECT '5106010010030349001' UNION ALL 
SELECT '5106010060020161000' UNION ALL 
SELECT '5106010060010170000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000'SELECT CASE WHEN CNT=1 THEN [地籍号] ELSE LEFT([地籍号],16)+RIGHT('000'+RTRIM(ID),3) END AS [地籍号]
FROM (
SELECT *,CNT=COUNT(*) OVER (PARTITION BY [地籍号]),ID=ROW_NUMBER() OVER (PARTITION BY LEFT([地籍号],16) ORDER BY GETDATE()) 
FROM @TB 
) T
ORDER BY [地籍号]
/*
地籍号
-------------------
5106010010020024001
5106010010020024002
5106010010020024003
5106010010020024004
5106010010020024005
5106010010030349001
5106010010030349002
5106010060010170000
5106010060020161000
*/

Answer 3

4楼的大哥 你的在SQL 2000中运行有错误 
服务器: 消息 156，级别 15，状态 1，行 15
在关键字 'OVER' 附近有语法错误。

Answer 4

3楼的方法不错 不过我这里就截取了一小段数据.我的数据有2万多条,而且我是要把数据修改了,用的应该是update 语句,希望高手们继续帮忙.

Answer 5

而且3楼的大哥最后3位排序还是有一点问题..
最终结果应该是4楼那样的.其实可以不用order by [地籍号]
这个

Answer 6

DECLARE @TB TABLE([地籍号] VARCHAR(19))
INSERT @TB 
SELECT '5106010010030349001' UNION ALL 
SELECT '5106010010030349001' UNION ALL 
SELECT '5106010060020161000' UNION ALL 
SELECT '5106010060010170000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000' UNION ALL 
SELECT '5106010010020024000'SELECT *,ID=IDENTITY(int,1,1) INTO # FROM @TB ORDER BY [地籍号]SELECT CASE WHEN CNT=1 THEN [地籍号] ELSE LEFT([地籍号],16)+RIGHT('000'+RTRIM(SEQ),3) END AS [地籍号] 
FROM (
SELECT T.*,CNT,SEQ=ID-(SELECT COUNT(*) FROM # WHERE [地籍号]<T.[地籍号]) 
FROM # T JOIN (SELECT [地籍号],COUNT(*) AS CNT FROM @TB GROUP BY [地籍号]) B
ON T.[地籍号]=B.[地籍号]
) TDROP TABLE #
/*
地籍号
-------------------
5106010010020024001
5106010010020024002
5106010010020024003
5106010010020024004
5106010010020024005
5106010010030349001
5106010010030349002
5106010060010170000
5106010060020161000(9 row(s) affected)
*/

Answer 7

DECLARE @a TABLE(a VARCHAR(100))
INSERT @a SELECT '5106010010030349001' 
union all select '5106010010030349001' 
union all select '5106010060020161000' 
union all select '5106010060010170000' 
union all select '5106010010020024000'  
union all select '5106010010020024000' 
union all select '5106010010020024000' 
union all select '5106010010020024000' 
union all select '5106010010020024000' DECLARE @m varchar(100),@n int
update @a set a=left(a,len(a)-3)+right('000'+ltrim(@n),3),@n=case when @m=left(a,len(a)-3) then isnull(@n,0)+1 else 0 end,@m=left(a,len(a)-3)select * from @a order by a
/*
a
----------------------------------------------------------------------------------------------------
5106010010020024000
5106010010020024001
5106010010020024002
5106010010020024003
5106010010020024004
5106010010030349000
5106010010030349001
5106010060010170000
5106010060020161000(9 行受影响)*/

Answer 8

DECLARE @a TABLE(a VARCHAR(100))
INSERT @a SELECT '5106010010030349001' 
union all select '5106010010030349001' 
union all select '5106010060020161000' 
union all select '5106010060010170000' 
union all select '5106010010020024000'  
union all select '5106010010020024000' 
union all select '5106010010020024000' 
union all select '5106010010020024000' 
union all select '5106010010020024000' DECLARE @m varchar(100),@n int
update @a set a=left(a,len(a)-3)+right('000'+ltrim(@n),3),@n=case when @m=left(a,len(a)-3) then isnull(@n,0)+1 else 1 end,@m=left(a,len(a)-3)
update a set a.a=left(a.a,len(a.a)-1)+'0' from @a a where not exists(select 1 from @a where left(a,len(a)-3)=left(a.a,len(a.a)-3) and a<>a.a)select * from @a order by a/*
a
----------------------------------------------------------------------------------------------------
5106010010020024001
5106010010020024002
5106010010020024003
5106010010020024004
5106010010020024005
5106010010030349001
5106010010030349002
5106010060010170000
5106010060020161000(9 行受影响)*/