想要实现如下的效果 a b c d 1100-1 1 1 2 1100-2 2 1 1 1100-3 1 1 1 ....a字段不是int类型的数据怎么办呢?
--临时表啊 --测试环境 declare @t table (a varchar(10), b int, c int,d int) insert into @t select '1001',1,1,2 union all select '1002',2,1,1 union all select '1001',1,1,1--查询 select identity(int,1,1) id,* into # from @t delete @t insert into @t select a+'-'+cast(id as varchar(10)),b,c,d from # select * from @t --结果 a b c d ---------- ----------- ----------- ----------- 1001-1 1 1 2 1002-2 2 1 1 1001-3 1 1 1(所影响的行数为 3 行) --删除环境 drop table #
可以自己手動寫了。 right(cast(10000+cast(('1' as int) as varchar),4)
a b c d
1100-1 1 1 2
1100-2 2 1 1
1100-3 1 1 1
....a字段不是int类型的数据怎么办呢?
--测试环境
declare @t table (a varchar(10), b int, c int,d int)
insert into @t select '1001',1,1,2
union all select '1002',2,1,1
union all select '1001',1,1,1--查询
select identity(int,1,1) id,* into # from @t
delete @t
insert into @t select a+'-'+cast(id as varchar(10)),b,c,d from #
select * from @t
--结果
a b c d
---------- ----------- ----------- -----------
1001-1 1 1 2
1002-2 2 1 1
1001-3 1 1 1(所影响的行数为 3 行)
--删除环境
drop table #
right(cast(10000+cast(('1' as int) as varchar),4)