求一更新语句,update

Question

如何更新表,表里现在的票号是不规范的,我想让每天规范下票号从1号开始,票号,序号,小号是主键票号，                                      序号，                                      小号
A23R200802270002                       1                                       1   
A23R200802270002                       2                                       1       
A23R200802270002                       3                                       1                   
A23R200802270011                       1                                       1   
A23R200802280001                       1                                       1   
A23R200802280001                       2                                       1   
A23R200802280032                       1                                       1   
A23R200802280114                       1                                       1   
想得到下列效果   
票号，                                                                           序号，                   小号，．．．．．   
A23R200802270001                       1                                       1   
A23R200802270001                       2                                       1       
A23R200802270001                       3                                       1                   
A23R200802270002                       1                                       1   
A23R200802280001                       1                                       1   
A23R200802280001                       2                                       1   
A23R200802280002                       1                                       1   
A23R200802280003                       1                                       1   

Answer 1

楼主给的数据结果怎么来的declare @A table(票号 nvarchar(20),序号 int,小号 int)
insert into @A
select 'A23R200802270002',1,1  union all
select 'A23R200802270002',2,1  union all
select 'A23R200802270002',3,1  union allselect 'A23R200802270011',1,1  union all
select 'A23R200802280001',1,1  union all
select 'A23R200802280001',2,1  union all
select 'A23R200802280032',1,1  union all
select 'A23R200802280114',1,1  select * from @A order by 小号,票号,序号
---结果集
票号                   序号          小号
-------------------- ----------- -----------
A23R200802270002     1           1
A23R200802270002     2           1
A23R200802270002     3           1
A23R200802270011     1           1
A23R200802280001     1           1
A23R200802280001     2           1
A23R200802280032     1           1
A23R200802280114     1           1(8 row(s) affected)

Answer 2

--自动创建编号函数
--规则A23R+今天的日期+今天添加的4位顺序号create function [dbo].[AutoCode](@date datetime)
returns varchar(11)
as
begin
declare @str varchar(11)
select @str=isnull(max(票号),'A23R'+right(datename(year,@date),4)+right('0'+right(datepart(month,@date),2),2)+right('00'+datename(day,@date),2)+'0000') 
from 表的名字
where 票号 like 'A23R'+right(datename(year,@date),4)+right('0'+right(datepart(month,@date),2),2)+right('00'+datename(day,@date),2)+'%'
select @str=left(@str,12)+right('000'+convert(varchar(3),convert(int,right(@str,3))+1),3)
return (@str)
end

Answer 3

--有个小错误
--更正
create function [dbo].[AutoCode](@date datetime)
returns varchar(16)
as
begin
declare @str varchar(16)
select @str=isnull(max(票号),'A23R'+right(datename(year,@date),4)+right('0'+right(datepart(month,@date),2),2)+right('00'+datename(day,@date),2)+'0000') 
from 表的名字
where 票号 like 'A23R'+right(datename(year,@date),4)+right('0'+right(datepart(month,@date),2),2)+right('00'+datename(day,@date),2)+'%'
select @str=left(@str,12)+right('0000'+convert(varchar(4),convert(int,right(@str,4))+1),4)
return (@str)
end

Answer 4

create table tb(票号 varchar(20), 序号 int, 小号 int)
insert into tb values('A23R200802270002', 1, 1 )
insert into tb values('A23R200802270002', 2, 1 )
insert into tb values('A23R200802270002', 3, 1 ) 
insert into tb values('A23R200802270011', 1, 1 ) 
insert into tb values('A23R200802280001', 1, 1 )
insert into tb values('A23R200802280001', 2, 1 )
insert into tb values('A23R200802280032', 1, 1 )
insert into tb values('A23R200802280114', 1, 1 )
goupdate tb set 票号 = n.新票号 from tb o,
(
  select m.* , 新票号 = left(票号,12) + right('0000'+cast(px as varchar),4) from
  (select t.* , px = (select count(distinct right(票号,4)) from tb where left(票号,12) = left(t.票号,12) and cast(right(票号,4) as int) < cast(right(t.票号,4) as int)) + 1 from tb t) m
) n
where o.票号 = n.票号 and o.序号 = n.序号 and o.小号 = n.小号select * from tbdrop table tb/*
票号                   序号          小号          
-------------------- ----------- ----------- 
A23R200802270001     1           1
A23R200802270001     2           1
A23R200802270001     3           1
A23R200802270002     1           1
A23R200802280001     1           1
A23R200802280001     2           1
A23R200802280002     1           1
A23R200802280003     1           1（所影响的行数为 8 行）
*/

Answer 5

数据是已经存在的啊,怎么在约束里做呢?现在的票号(A23R+日期+张数)每一天的第一张票号不一定是1号票,比如A23R200501010032,就是32号票号,我就想把每天的第一张票号从1向后累计更新,得到目的表啊.
ddddddddddddddddddddddd                                   

Answer 6

潇洒老乌龟(爱新觉罗.毓华),楼主没有能读懂你的Update语句，哎！！楼方７楼的方法是对的，请仔细读！