一个表 三个字段 表数据如下:
transid refdate id
1000 2008-05-26
1001 2008-05-27
1002 2008-05-26
1003 2008-05-27
1004 2008-05-27
通过更新变
transid refdate id
1000 2008-05-26 200805260001
1001 2008-05-27 200805270001
1002 2008-05-26 200805260002
1003 2008-05-27 200805270002
1004 2008-05-27 200805270003 就是按 日期 ,transid排序 后 1000 在 2008-05-26第一条(号码是20080526xxxx) 1002 在第二条(号码是20080526xxxx)1001 和1003和1004 依次为 (号码是20080527) 的第1 \ 2 \3 和记录
transid refdate id
1000 2008-05-26
1001 2008-05-27
1002 2008-05-26
1003 2008-05-27
1004 2008-05-27
通过更新变
transid refdate id
1000 2008-05-26 200805260001
1001 2008-05-27 200805270001
1002 2008-05-26 200805260002
1003 2008-05-27 200805270002
1004 2008-05-27 200805270003 就是按 日期 ,transid排序 后 1000 在 2008-05-26第一条(号码是20080526xxxx) 1002 在第二条(号码是20080526xxxx)1001 和1003和1004 依次为 (号码是20080527) 的第1 \ 2 \3 和记录
提示错误
高手帮帮改改---重置年月日流水号不正确的记录
update ojdt set dateid=null
where CONVERT(varchar(12) ,refdate, 112 )<>left(dateid,8)declare @refdate date
declare @dateid nvarchar(12)
declare mycursor SCROLL CURSOR FOR select refdate,dateid from ojdt where dateid is null order by refdate,idopen mycursor
fetch next from mycursor into @refdate,@dateid
while(@@fetch_status = 0)
begin
Update ojdt set dateid=cast(max(dateid) as int)+1 where dateid is null and refdate=@refdate
fetch next from mycursor into @refdate,@dateid
end
close mycursor
deallocate mycursor
GO
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (transid int,refdate datetime,id varchar(20))
insert into #T
select 1000,'2008-05-26',null union all
select 1001,'2008-05-27',null union all
select 1002,'2008-05-26',null union all
select 1003,'2008-05-27',null union all
select 1004,'2008-05-27',null;
with cte as
(
select
transid,refdate,id,
rnkid=row_number() over(partition by convert(varchar(10),refdate,120) order by transid)
from #T
)
update cte
set id=convert(varchar(8),refdate,112)+right('0000'+ltrim(rnkid),4)
select * from #T/*
transid refdate id
----------- ----------------------- --------------------
1000 2008-05-26 00:00:00.000 200805260001
1001 2008-05-27 00:00:00.000 200805270001
1002 2008-05-26 00:00:00.000 200805260002
1003 2008-05-27 00:00:00.000 200805270002
1004 2008-05-27 00:00:00.000 200805270003(5 行受影响)*/
你的我运行不了
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (transid int,refdate datetime,id varchar(20))
insert into #T
select 1000,'2008-05-26',null union all
select 1001,'2008-05-27',null union all
select 1002,'2008-05-26',null union all
select 1003,'2008-05-27',null union all
select 1004,'2008-05-27',nullupdate t
set id=convert(varchar(8),refdate,112)+
right(ltrim((select count(*) from #T where transid<=t.transid and refdate=t.refdate)),4)
from #T as tselect * from #T/*
transid refdate id
----------- ----------------------- --------------------
1000 2008-05-26 00:00:00.000 200805261
1001 2008-05-27 00:00:00.000 200805271
1002 2008-05-26 00:00:00.000 200805262
1003 2008-05-27 00:00:00.000 200805272
1004 2008-05-27 00:00:00.000 200805273(5 行受影响)
*/
因为 可能有一个日期 有100个id
但 max(dateid) 是null
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (transid int,refdate datetime,id varchar(20))
insert into #T
select 1000,'2008-05-26',null union all
select 1001,'2008-05-27',null union all
select 1002,'2008-05-26',null union all
select 1003,'2008-05-27',null union all
select 1004,'2008-05-27',nullupdate t
set id=convert(varchar(8),refdate,112)+
right('0000'+ltrim((select count(*) from #T where transid<=t.transid and refdate=t.refdate)),4)
from #T as tselect * from #T/*
transid refdate id
----------- ----------------------- --------------------
1000 2008-05-26 00:00:00.000 200805260001
1001 2008-05-27 00:00:00.000 200805270001
1002 2008-05-26 00:00:00.000 200805260002
1003 2008-05-27 00:00:00.000 200805270002
1004 2008-05-27 00:00:00.000 200805270003(5 行受影响)
*/
是不是 找到当前日期 比当前单号 小的记录总数 来确定当前的 记录位置
right('0000'+ltrim((select count(*) from #T where transid<=t.transid and refdate=t.refdate)),4)
是先拼接字符串
即使长度超过 4为
但一个right 4 就搞定了
谢谢