表中有一列字符如下:(100行)
ZX000001
ZX000002
...
...
ZX000100现在想将这列值变成跳号的形式(还是100行)
ZX000001
ZX000006
ZX000011
....
....
ZX000500
ZX000001
ZX000002
...
...
ZX000100现在想将这列值变成跳号的形式(还是100行)
ZX000001
ZX000006
ZX000011
....
....
ZX000500
解决方案 »
- 数据库image类型的问题
- sql server 两个表的查询
- 为方便打印 求power builder,谢谢!
- 問個很基礎的問題﹐raiserror(N'源数据库"%s"不存在',1,16,@sdbname)
- 在VB中如何引用SQL Server中执行DTS包时的成功和错误信息(急.急.急.急)
- 如何在Windows Server 2003中安装SQL Server 2000呢?
- 关于delete超时的问题
- sql server 2005怎么连接sql server 2000数据库
- 关于bcp问题
- 创建数据库的问题!!!能够说清楚的马上给分!
- datetime的插入
- 询问一个SQL如何实现判断查询的问题
declare @a table(a varchar(100))
insert into @a
select 'ZX000001' union all
select 'ZX000002' union all
select 'ZX000003' union all
select 'ZX000004' union all
select 'ZX000005' union all
select 'ZX000006' union all
select 'ZX000007' union all
select 'ZX000008' union all
select 'ZX000009' union all
select 'ZX000010'
select * from @a --原数据
;
with tb as(
select number+1 b,a='ZX0000'+case when CONVERT(varchar,number*5+1)>10 then CONVERT(varchar,number*5+1)
when CONVERT(varchar,number*5+1)<10 then '0'+CONVERT(varchar,number*5+1) end
from master..spt_values where type='p' and number between 0 and 9)
,tc as(
select ROW_NUMBER() over(order by a) number,* from @a
)
update tc set a=tb.a from tb where b=number
select * from @a --更新后的数据用10行举个例子..
5C4CA9000001
5C4CA9000002
5C4CA9000003
--1.建表
if OBJECT_ID('a') is not null
drop table a
gocreate table a
(
old_value varchar(100), --原来的连续的值
new_value varchar(100) --经过更新后,现在不连续的值
)
go
--2.生产连续的100条测试数据,从ZX000001-ZX000100
;with t
as
(
select 1 as col
union all
select col + 1 as col
from t
where t.col < 100
),tt
as
(
select 'ZX' + right('000000' + CAST(t.col as varchar),6) as col
from t
)--把产生的连续值,插入到表a中
insert into a(old_value,new_value)
select col,col from tt
--3.更新值
select * from adeclare @i int
declare @j intset @i = 0;update a
set @j = 1 + @i * 5, --每个值都是前一个值加上5
@i = @i + 1,
new_value = 'ZX' + right('000000' + cast(@j as varchar) ,6)