查询,查询.

[liangCK]

在ITPUB看到的.蛮复杂..不知道有没有更好的办法?
有一个表名称为customer,其中有一字段为name,类型为varchar(15).
name列数据如下:
name
33电话
33电视
33电脑
34电话
34电话
34电视
33电脑
33电池
31电话
31电视
31电脑
32电话
32电视
32电脑

现在想变成以下内容:
name
3301电话
3302电视
3303电脑
3401电话
3402电话
3403电视
3304电脑
3305电池
3101电话
3102电视
3103电脑
3201电话
3202电视
3203电脑
也就是在name列中前两位数字与后面汉字之间插入两位编号,编号以前两位相同的为一类增加,如33类变成3301,3302,3303...;34类变成3401,3402,3403,...

[fcuandy]

相当简单.
我设他的序号是集中在一起的.
即 33在前面连续出现3次后,中间有了别的号如34,而在34之后不再出现33. 其它数字同理
如果未集中一起,那么,先order by 排次序.

DECLARE @t TABLE(v VARCHAR(15))
INSERT @t SELECT '33电话'  
UNION ALL SELECT '33电视'  
UNION ALL SELECT '33电脑'  
UNION ALL SELECT '34电话'  
UNION ALL SELECT '34电话'  
UNION ALL SELECT '34电视'  
UNION ALL SELECT '33电脑'  
UNION ALL SELECT '33电池'  
UNION ALL SELECT '31电话'  
UNION ALL SELECT '31电视'  
UNION ALL SELECT '31电脑'  
UNION ALL SELECT '32电话'  
UNION ALL SELECT '32电视'  
UNION ALL SELECT '32电脑'

DECLARE @cid INT,@idx INT,@old INT
UPDATE @t SET @cid=LEFT(v,2),@idx=CASE WHEN @old=@cid THEN @idx+1 ELSE 1 END,@old=@cid,v=RTRIM(@cid) + RIGHT('00' + RTRIM(@idx),2) + STUFF(v,1,2,'')

[中国风]

create  TABLE #(Name VARCHAR(15))
INSERT # SELECT '33电话'  
UNION ALL SELECT '33电视'  
UNION ALL SELECT '33电脑'  
UNION ALL SELECT '34电话'  
UNION ALL SELECT '34电话'  
UNION ALL SELECT '34电视'  
UNION ALL SELECT '33电脑'  
UNION ALL SELECT '33电池'  
UNION ALL SELECT '31电话'  
UNION ALL SELECT '31电视'  
UNION ALL SELECT '31电脑'  
UNION ALL SELECT '32电话'  
UNION ALL SELECT '32电视'  
UNION ALL SELECT '32电脑'
go
alter table # add ID int identity(1,1)

go
---查询
select 
	Name=left(Name,2)+(select right('00'+rtrim(count(1)),2) from # where left(Name,2)=left(t.Name,2) and ID!>t.ID)+right(Name,2)
from 
	# t
go

update t
set	Name=left(Name,2)+(select right('00'+rtrim(count(1)),2) from # where left(Name,2)=left(t.Name,2) and ID!>t.ID)+stuff(Name,1,2,'')
from 
	# t
go
alter table # drop column ID--删除辅助列

go
select * from #

Name            
--------------- 
3301电话
3302电视
3303电脑
3401电话
3402电话
3403电视
3304电脑
3305电池
3101电话
3102电视
3103电脑
3201电话
3202电视
3203电脑

（所影响的行数为 14 行）

[中国风]

在不改变表的顺序的情况下，需要用辅助列..
如SQL05:
row_number()--生成排序序列
select *
from
(select
*
,row_number()over(order by Name)
from
ta t
)

[pt1314917]

借用临时表
create TABLE t3(v VARCHAR(15))
INSERT into t3 SELECT '33电话'  
UNION ALL SELECT '33电视'  
UNION ALL SELECT '33电脑'  
UNION ALL SELECT '34电话'  
UNION ALL SELECT '34电话'  
UNION ALL SELECT '34电视'  
UNION ALL SELECT '33电脑'  
UNION ALL SELECT '33电池'  
UNION ALL SELECT '31电话'  
UNION ALL SELECT '31电视'  
UNION ALL SELECT '31电脑'  
UNION ALL SELECT '32电话'  
UNION ALL SELECT '32电视'  
UNION ALL SELECT '32电脑'


select id=identity(int,1,1),* into #temp from t3 group by v
select left(v,2)+right('00'+cast((select count(1) from #temp
 where id<a.id and left(v,2)=left(a.v,2))+1 as varchar),2)+right(v,len(v)-2) v from #temp a