求助-关于生成流水号

[liuml2012]

现在有两张表,一个是主表TB,一个是从外部导入进来的临时表TB1

把临时表的数据插入到主表中,主表中有一个流水号字段,

流水号字段生成规则
类型+年+月+日+5位流水号(如OP12042700001)

有一个条件,每天生成的后五位流水号,都是从0001开始.




先谢过!

[liuml2012]

流水号字段生成规则
类型+年+月+日+5位流水号(如OP12042700001)

类型OP是固定!

[开着拖拉机泡妞]

在学习中遇到这个问题 
数据库里有编号字段 
BH00001 
BH00002 
BH00003 
BH00004 
如何实现自动增长

 

--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
    RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

--插入资料
BEGIN TRAN
    INSERT tb(col) VALUES(1)
    INSERT tb(col) VALUES(2)
    INSERT tb(col) VALUES(3)
    DELETE tb WHERE col=3
    INSERT tb(col) VALUES(4)
    INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN

--显示结果
SELECT * FROM tb
/*--结果
BH         col 
---------------- ----------- 
BH000001  1
BH000002  2
BH000003  4
BH000004  14
--*/

 

create table tb
(id int identity,
name varchar(10),
code as 'BH'+right('0000'+cast(id as varchar),5))
go
insert tb(name) select 'A'
union all select 'B'
union all select 'C'
union all select 'D'

select * from tb

drop table tb

/*
id          name       code         
----------- ---------- ------------ 
1           A          BH00001
2           B          BH00002
3           C          BH00003
4           D          BH00004

（所影响的行数为 4 行）
*/

参考，严格说自己修改就可以了

[liuml2012]

[Quote=引用 2 楼 的回复:]

SQL code

在学习中遇到这个问题
数据库里有编号字段
BH00001
BH00002
BH00003
BH00004
如何实现自动增长

[/Quote]
你这个是一直自动增长,我有一个条件是每天都从00001开始增长,接着求处理方法
自己在顶.

[唐诗三百首]

楼主没讲划分天的依据是什么,

依当前天还是TB1里的某个字段?

[水向东流时光怎么偷]

[Quote=引用 4 楼 的回复:]

楼主没讲划分天的依据是什么,

依当前天还是TB1里的某个字段?
[/Quote]
依据当天,大大

[开着拖拉机泡妞]

--如何生成流水号:
go
if OBJECT_ID('test')is not null
drop table test
go
create table test(
BH VARCHAR(15),
A varchar(10),
B varchar(10),
C varchar(10)
)
go
insert test
select '','a','b','c' union all
select '','d','e','f' union all
select '','g','h','i' union all
select '','j','k','l' union all
select '','m','n','o' union all
select '','p','q','r'

--创建函数：
go
if OBJECT_ID('fun_tracy')is not null
drop function fun_tracy
go
create function fun_tracy (@id int)
returns varchar(15)
as
begin
declare @BH varchar(15)
set @BH=''
declare @date varchar(10)
select @date='OP'+right(replace(convert(varchar(10),getdate(),120),'-',''),6)
select @BH=@date+right('00000'+ltrim(@id),5)
return @BH
end


--处理表数据:
alter table test add id int identity
go

update test
set BH=dbo.fun_tracy(id)
ALTER TABLE TEST DROP COLUMN ID
GO
select * from test
/*
BH	A	B	C
OP12042700001	a	b	c
OP12042700002	d	e	f
OP12042700003	g	h	i
OP12042700004	j	k	l
OP12042700005	m	n	o
OP12042700006	p	q	r
*/

--这个可否满足？？

[开着拖拉机泡妞]

--如何生成流水号:
go
if OBJECT_ID('test')is not null
drop table test
go
create table test(
BH VARCHAR(15),
A varchar(10),
B varchar(10),
C date
)
go
insert test
select null,'a','b','2011-04-01' union all
select null,'c','d','2011-04-01' union all
select null,'e','f','2011-04-02' union all
select null,'g','h','2011-04-02' union all
select null,'i','j','2012-04-03' union all
select null,'k','l','2012-04-03' union all
select null,'m','n','2012-05-03' union all
select null,'o','p','2012-05-03' 

--创建函数：
go
if OBJECT_ID('fun_tracy')is not null
drop function fun_tracy
go
create function fun_tracy (@id int,@date date)
returns varchar(15)
as
begin
declare @BH varchar(15)
set @BH=''
declare @time varchar(10)
select @time='OP'+right(replace(convert(varchar(10),@date,120),'-',''),6)
select @BH=@time+right('00000'+ltrim(@id),5)
return @BH
end


--处理表数据:

alter table test add id int identity
go

update test
set BH=dbo.fun_tracy(id,C)
ALTER TABLE TEST DROP COLUMN ID
GO
select * from test
/*
BH	A	B	C
OP11040100001	a	b	2011-04-01
OP11040100002	c	d	2011-04-01
OP11040200003	e	f	2011-04-02
OP11040200004	g	h	2011-04-02
OP12040300005	i	j	2012-04-03
OP12040300006	k	l	2012-04-03
OP12050300007	m	n	2012-05-03
OP12050300008	o	p	2012-05-03
*/


如果需要根据表里面的时间来处理的话就这样

[ghrui]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran
select @vNextCommLsh = '0'
select @CurrDate = convert(varchar(8), getdate(), 112), @CurrCommLsh = CurrCommLsh from Syssetup where maxuser=1000-- with (tablockx)
select @MaxCommLsh = isnull(max(lsh), '0') from CardUse where substring(lsh, 1 ,8) = @CurrDate
while @vNextCommLsh <= @MaxCommLsh
begin
if @CurrDate = substring(@CurrCommLsh, 1, 8)
select @vNextCommLsh = @CurrDate + substring(convert(varchar(10),100000001+convert(int, substring(@CurrCommLsh, 9, 8))), 2, 8)
--select @vNextCommLsh = @CurrDate + substring(100000001+convert(bigint, '111'), 2, 8)
else
select @vNextCommLsh = @CurrDate + '00000001'
select @CurrCommLsh = @vNextCommLsh
end
update syssetup with(rowlock) set CurrCommLsh = @vNextCommLsh where maxuser=1000
if @@Error <> 0
rollback tran
else
commit tran

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
	DROp TABLE tba
END
GO
CREATE TABLE Tba
(
	TDate VARCHAR(10),
	Num VARCHAR(100)
) 

GO


INSERT INTO Tba
SELECT '2012-04-28','OP12042800001' UNION
SELECT '2012-04-28','OP12042800002' UNION
SELECT '2012-04-28','OP12042800003' UNION
SELECT '2012-04-28','OP12042800004' 

GO


IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'InsertNum')
BEGIN
	DROP FUNCTION InsertNum
END
GO
CREATE FUNCTION InsertNum(@TDate VARCHAR(10))
RETURNS VARCHAR(100)
AS

BEGIN
DECLARE @Num VARCHAR(100)
DECLARE @Num_Int INT
SET @TDate = REPLACE(@TDate,'-','')
SET @TDate = RIGHT(@TDate,6)

SELECT @Num_Int = MAX(CAST(RIGHT(Num,5) AS INT)) + 1
FROM Tba
WHERE SUBSTRING(Num,3,6) = @TDate

IF @Num_Int IS NULL 
BEGIN
	SET @Num_Int = 1
END

SET @Num = 'OP' + @TDate + REPLICATE('0',5-LEN(CAST(@NUm_int AS VARCHAR(10)))) + CAST(@NUm_int AS VARCHAR(10))


RETURN @Num
END

GO

INSERT INTO Tba 
SELECT '2012-04-28',DBO.InsertNum('2012-04-28') UNION
SELECT '2012-04-29',DBO.InsertNum('2012-04-29')

SELECT * FROM tba

TDate	Num
2012-04-28	OP12042800001
2012-04-28	OP12042800002
2012-04-28	OP12042800003
2012-04-28	OP12042800004
2012-04-28	OP12042800005
2012-04-29	OP12042900001

[ahidden]

创建生成流水号的触发器

create table test(id varchar(18),  --流水号,日期(8位)+时间(4位)+流水号(4位)
name varchar(10)  --其他字段
)

go
--创建生成流水号的触发器
create trigger t_insert
on test
INSTEAD OF insert
as
declare @id varchar(18),@id1 int,@head varchar(12)
select * into #tb from inserted
set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')
select @id=max(id) from test where id like @head+'%'
if @id is null
set @id1=0
else
set @id1=cast(substring(@id,13,4) as int)
update #tb set @id1=@id1+1
,id=@head+right('0000'+cast(@id1 as varchar),4)
insert into test select * from #tb
go


--插入数据,进行测试
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'

--修改系统时间,再插入数据测试一次
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'

--显示测试结果
select * from test


--删除测试环境
drop table test

[ahidden]

create table test(id varchar(18),  --流水号,日期(8位)+时间(4位)+流水号(4位)
name varchar(10)  --其他字段
)

go
--创建生成流水号的触发器
create trigger t_insert
on test
INSTEAD OF insert
as
declare @id varchar(18),@id1 int,@head varchar(12)
select * into #tb from inserted
set @head='OP'+right(replace(convert(varchar(10),getdate(),120),'-',''),6)
select @id=max(id) from test where id like @head+'%'
if @id is null
set @id1=0
else
set @id1=cast(substring(@id,13,4) as int)
update #tb set @id1=@id1+1
,id=@head+right('0000'+cast(@id1 as varchar),4)
insert into test select * from #tb
go
--插入数据,进行测试
insert into test(name)  --系统日期为了2012-04-28
select 'aa'
union all select 'bb'
union all select 'cc'
go
select * from test
/*
id                 name
------------------ ----------
OP1204280001       aa
OP1204280002       bb
OP1204280003       cc
(3 行受影响)
*/
--修改系统时间,再插入数据测试一次
insert into test(name) --系统日期为了2012-04-29
select 'aa'
union all select 'bb'
union all select 'cc'
go
select * from test
/*
id                 name
------------------ ----------
OP1204280001       aa
OP1204280002       bb
OP1204280003       cc
OP1204280001       aa
OP1204280002       bb
OP1204280003       cc

(6 行受影响)
*/
--删除测试环境
drop table test

[ahidden]

--测试数据2,没有修改系统时间,所以显示结果没有变
select * from test
/*id                 name
------------------ ----------
OP1204290001       aa
OP1204290002       bb
OP1204290003       cc
OP1204280001       aa
OP1204280002       bb
OP1204280003       cc

(6 行受影响)

*/