select count(1) as 今天入库的记录条数 from tb where datediff(dd,生产日期,getdate())=0
create table tb(批号 varchar(20)) insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4) insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4) insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4) insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4) insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4) insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4) insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4) select * from tb go drop table tb /* 批号 -------------------- 201105150001 201105150002 201105150003 201105150004 201105150005 201105150006 201105150007(7 行受影响) */
楼主应该从现有的表里查出今天记录最大的那条,不能按记录的条数去设置主键值。如果你今天录了10条记录,但删除了一条,不是10的那条,那你再次插入的时候肯定还是10,会出现重复,无法插入的情况! --主键id为字符型 select max(cast(id as bigint)) + 1 as id from tb where substring(id,1,8) = convert(varchar(8),getdate(),112)
Create table tb(批号 varchar(20),产品名称 varchar(50),单价 decimal(18,2),生产日期 datetime) insert into tb select '201105160001','产品名称1',1.49,GETDATE() union all select '201105160002','产品名称2',2.49,GETDATE() union all select '201105160003','产品名称3',3.49,GETDATE() union all select '201105160004','产品名称4',4.49,GETDATE() union all select '201105160005','产品名称5',5.49,GETDATE() union all select '201105160006','产品名称6',6.49,GETDATE() union all select '201105160007','产品名称',7.49,GETDATE() Go alter procedure p_getOrderNo as Begin declare @orderNo varchar(20) set @orderNo = CONVERT(varchar(8),GETDATE(),112) declare @temp varchar(20) select @temp = MAX(批号) from tb where LEFT(批号,8) = @orderNo if @temp is not null set @orderNo = @orderNo +RIGHT('0000'+CAST((CAST(RIGHT(@temp,4) as int) + 1) as varchar(5)),4) else set @orderNo = @orderNo + '0001' select @orderNo End Goexec p_getOrderNo
convert(varchar(10),getdate(),112)+LTRIM(right('0000'+LTRIM(7+1),4))
/*------------------
201105150008(1 行受影响)
*/
insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4)
insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4)
insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4)
insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4)
insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4)
insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4)
insert into tb select convert(varchar(8),getdate(),112)+right('000'+ltrim(isnull((select count(*) from tb where left(批号,8)=convert(varchar(8),getdate(),112)),0)+1),4)
select * from tb
go
drop table tb
/*
批号
--------------------
201105150001
201105150002
201105150003
201105150004
201105150005
201105150006
201105150007(7 行受影响)
*/
楼主应该从现有的表里查出今天记录最大的那条,不能按记录的条数去设置主键值。如果你今天录了10条记录,但删除了一条,不是10的那条,那你再次插入的时候肯定还是10,会出现重复,无法插入的情况!
--主键id为字符型
select max(cast(id as bigint)) + 1 as id
from tb
where substring(id,1,8) = convert(varchar(8),getdate(),112)
insert into tb
select '201105160001','产品名称1',1.49,GETDATE() union all
select '201105160002','产品名称2',2.49,GETDATE() union all
select '201105160003','产品名称3',3.49,GETDATE() union all
select '201105160004','产品名称4',4.49,GETDATE() union all
select '201105160005','产品名称5',5.49,GETDATE() union all
select '201105160006','产品名称6',6.49,GETDATE() union all
select '201105160007','产品名称',7.49,GETDATE()
Go
alter procedure p_getOrderNo
as
Begin
declare @orderNo varchar(20)
set @orderNo = CONVERT(varchar(8),GETDATE(),112)
declare @temp varchar(20)
select @temp = MAX(批号) from tb where LEFT(批号,8) = @orderNo
if @temp is not null
set @orderNo = @orderNo +RIGHT('0000'+CAST((CAST(RIGHT(@temp,4) as int) + 1) as varchar(5)),4)
else
set @orderNo = @orderNo + '0001'
select @orderNo
End
Goexec p_getOrderNo