假设字段bianhao为:'0001','0002'
select IDENTITY(int, 1,1) AS ID_Num,*
INTO #tmp
from t1 order by bianhaoupdate t1 set bianhao=right(b.id+10000,4)
from t1 a ,#tmp b where a.bianhao=b.bianhaodrop table #tmp
select IDENTITY(int, 1,1) AS ID_Num,*
INTO #tmp
from t1 order by bianhaoupdate t1 set bianhao=right(b.id+10000,4)
from t1 a ,#tmp b where a.bianhao=b.bianhaodrop table #tmp
表中已有自增列,不能加怎么处理,表中已有的自增列断号.谢谢
Select right(b.id+10000,4)
from t1 a
Left Join #t_temp b
On a.bianhao=b.bianhaodrop table #t_temp
SELECT CAST('0001' AS varchar(20)) AS col INTO #1
UNION ALL SELECT '0003'
UNION ALL SELECT '0004'ALTER TABLE #1 ADD ID int IDENTITY(1,1)SELECT col, RIGHT('0000' + CAST(ID AS varchar(4)), 4) FROM #1DROP TABLE #1
set
字段名 = right('0000'+cast(b.cnt as varchar(4)),4)
from
tablename a,
(select c.字段名,cnt=count(d.*)
from tablename c,tablename d
where c.字段名>=d.字段名 group by c.字段名) b
where
a.字段名 = b.字段名
INTO #t_temp
from t1 update a set bianhao =
right(b.ID +10000,4)
from t1 a , #t_temp b
Where a.bianhao=b.bianhao drop table #t_temp
不好意思刚才发错了~
select '0002'
union
select '0003'
union
select '0004'
union
select '0018'
declare @Min int, @Max int, @i int
select aID=CONVERT(INT,aID) into #TMP01 from #test2select @Min=MIN(aID), @Max=MAX(aiD) from #TMP01create table #TMP02 (aID VARCHAR(20))SET @i=@Min
while @i <= @Max
BEGIN
INSERT INTO #TMP02(aiD) values( right(@i+10000,4))
SET @i=@i+1
ENDselect * from #TMP02drop table #TMP02
drop table #TMP01
drop table #test2
表中已有自增列,不能加怎么处理,表中已有的自增列断号.谢谢
-------------------------->
那就更简单了:
假设字段bianhao为:'0001','0002',id:为test自增列。表为:test
update test set bianhao=RIGHT((select count(1)
from test where id<=a.id)+10000 ,4) from test a
update test set bianhao=RIGHT((select count(1)
from test where id<=a.id)+10000 ,4) from test aprint right(10000+2341,4)+'a'
create table test ([ID] [int] IDENTITY (1, 1) NOT NULL constraint pk_id primary key ,bianhao varchar(10),fld varchar(10))
go--测式
insert into test (FLD)
select '01'
insert into test (FLD)
select '02'
insert into test (FLD)
select '03'
insert into test (FLD)
select '04'
delete from test where id=2update test set bianhao=RIGHT((select count(1)
from test where id<=a.id)+10000 ,4) from test a
select * from test
--结果
ID bianhao fld
----------- ---------- ----------
1 0001 01
3 0002 03
4 0003 04(所影响的行数为 3 行)*/
go
--删除测试环境
drop table test
create table # (a int)
insert into #
select 1 union
select 3 union
select 5 union
select 6 union
select 8 union
select 9 union
select 11 union
select 15 insert into # select a+15 from #
insert into # select a+30 from #select * from #--更新该列为连续数列
declare @i intset @i=0
update # set a=1
update # set @i=a=a+@iselect * from #