---转贴邹建
--自已做标识列的例子--流水号:日期+当日编号:--创建得到最大id的函数
create function f_getid()
returns varchar(13)
as
begin
declare @id varchar(13),@dt varchar(8)
select @dt=dt from v_getdate
select @id=max(id) from tb where id like @dt+'-%'
if @id is null
set @id=@dt+'-'+'0001'
else
set @id=@dt+'-'+right('0000'+cast(cast(right(@id,4) as int)+1 as varchar),4)
return(@id)
end
go--创建表
create table tb(id varchar(20) default dbo.f_getid() primary key,name varchar(10))
go--创建视图,得到当前日期(因为函数中不能使用getdate())
create view v_getdate as select dt=convert(varchar,getdate(),112)
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb
go--删除环境
drop table tb
drop view v_getdate
drop function f_getid
--自已做标识列的例子--流水号:日期+当日编号:--创建得到最大id的函数
create function f_getid()
returns varchar(13)
as
begin
declare @id varchar(13),@dt varchar(8)
select @dt=dt from v_getdate
select @id=max(id) from tb where id like @dt+'-%'
if @id is null
set @id=@dt+'-'+'0001'
else
set @id=@dt+'-'+right('0000'+cast(cast(right(@id,4) as int)+1 as varchar),4)
return(@id)
end
go--创建表
create table tb(id varchar(20) default dbo.f_getid() primary key,name varchar(10))
go--创建视图,得到当前日期(因为函数中不能使用getdate())
create view v_getdate as select dt=convert(varchar,getdate(),112)
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb
go--删除环境
drop table tb
drop view v_getdate
drop function f_getid
create function f_getid()
returns varchar(12)
as
begin
declare @id varchar(12),@dt varchar(8)
select @dt=dt from v_getdate
select @id=max(id) from tb where id like @dt+'%'
if @id is null
set @id=@dt+'001'
else
set @id=@dt+right('000'+cast(cast(right(@id,3) as int)+1 as varchar),3)
return(@id)
end
go--创建表
create table tb(id varchar(20) default dbo.f_getid() primary key,name varchar(10))
go--创建视图,得到当前日期(因为函数中不能使用getdate())
create view v_getdate as select dt=convert(varchar,getdate(),112)
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tbgo
--删除环境
drop table tb
drop view v_getdate
drop function f_getid
from tablename
select cast(year(getdate()) as varchar)+
+case when month(getdate())<9 then '0'+cast(month(getdate()) as varchar)
else cast(month(getdate()) as varchar)
end
+case when day(getdate())<9 then '0'+cast(day(getdate()) as varchar)
else cast(day(getdate()) as varchar)
end
(
id varchar(10),
name varchar(1000)
)
gocreate procedure get_id @str varchar(10) OUTPUT
as
begin
declare @num intselect @str=cast(year(getdate()) as varchar)+
+case when month(getdate())<9 then '0'+cast(month(getdate()) as varchar)
else cast(month(getdate()) as varchar)
end
+case when day(getdate())<9 then '0'+cast(day(getdate()) as varchar)
else cast(day(getdate()) as varchar)
endselect @num=count(1)
from table_name
where left(id,8)=@strselect @str=@str+cast(@num as varchar)end
--以你的表还有一个日期字段为例,在要生成序号前,先查询出当天是否已经有记录
declare @xh char(11) --序号
select top 1 @xh=序号 from 表 where 日期字段=当天 order by 序号 desc
if @@rowcount=0 --当天没有记录
set @xh=convert(char(8),当天,112) + '001'
else --有记录,@xh为最大一条的序号
set @xh=convert(char(8),当天,112) + right('000' + cast(cast(right(@xh,3) as bigint)+1 as varchar(3)),3) --取出后三位,再加1,生成最新的序号
象select @id=max(id) from tb where id like @dt+'%'或select @num=count(1)
from table_name
where left(id,8)=@str
都有可能产生并发问题,而且如果记录多的话,速度慢,且会出现重复序号的可能性就越大。
建表
create table sntb
(
snday varchar(8),
snid int
)
数据格式
snday snid
20040801 1
20040802 2create proc getid
@daystr varchar(8),
@str varchar(12) output
as
begin
declare @snid varchar(12)
select @snid=snid from sntb where snday=@daystr
if @@rowcount=0
begin
set @snid=1
insert into sntb(snday,snid) values(@daystr,@snid)
set @str = @daystr + right(('000'+cast(@snid as varchar)),3)
end
else
begin
set @snid=@snid+1
update sntb set snid=@snid where snday=@daystr
set @str = @daystr + right(('000'+cast(@snid as varchar)),3)
endend
但这种方法的好处是不用执行模糊查询like,不用max函数,且记录少,查找时间少,并发可能性底。目前一个问题是如何完全解决并发问题
@ls_sign char(1),
@ret_value char(13) output
ASDeclare @ls_max numeric
Declare @ls_max1 numeric
Declare @ls_djh char(13)
declare @ls_vocation char(4)
declare @v1 int
Declare @var2 char(13)Begin transaction
---
if exists(select * from djh_wh where substring(djh,1,1)=@ls_sign)-----存在赠修改信息
begin
-- Print('存在了相应单据信息!')
select @ls_djh=(select SUBSTRING(djh,10,4) from djh_wh where substring(djh,1,1)=@ls_sign and substring(djh,2,8)=convert(char(8),getdate(),112))
if (@ls_djh is null) or(rtrim(ltrim(@ls_djh))='')
begin
--select @ls_djh='0001'
select @ls_djh=(@ls_sign+convert(char(8),getdate(),112)+'0001')
-- print('存在了相应单据信息!,本日的没有')
Update djh_wh with(rowlock) set djh=@ls_djh where substring(djh,1,1)=@ls_sign
select @ret_value =@ls_djh
end
else
begin-- Print('Ok ,pass1')
--------
select @var2=str(convert(dec,@ls_djh)+1,4)
-- print('dddddd'+@var2)
select @v1=charindex(' ',@var2)
while @v1>0 and @v1 < 4 ----注意该条件-----
begin
select @var2=stuff(@var2,@v1,1,'0')
select @v1=charindex(' ',@var2)
end --------
select @ls_djh=(@ls_sign+convert(char(8),getdate(),112)+@var2)
Update djh_wh with(rowlock) set djh=@ls_djh where substring(djh,1,1)=@ls_sign
select @ret_value =@ls_djh
end
end
else---不存在则要新增
begin
select @ls_djh=(@ls_sign+convert(char(8),getdate(),112)+'0001')
insert into djh_wh with(rowlock)(djh) values(@ls_djh)
select @ret_value =@ls_djh
end
if @@error<>0
rollback transaction
commit transaction
go
ID INT AUTO
CODE VARCHARCREATE TRIGGER TRIGGER_NAME ON TABLENAME
FOR INSERT
AS
DECLARE @TODAY VARCHAR(8), @Y VARCHAR(4),@M VARCHAR(2),@D VARCHAR(2)
DECLARE @ID INT, @MAX INT, @COUNT INT
DECLARE @MAXSTR VARCHAR(5)SET @Y = CAST(YEAR(GETDATE()) AS VARCHAR(4))
SET @M = CAST(MONTH(GETDATE()) AS VARCHAR(2))
SET @D = CAST(DAY(GETDATE()) AS VARCHAR(2))
IF(LEN(@M)<2) SET @M = '0'+@M
IF(LEN(@D)<2) SET @D = '0'+@D
SET @TODAY = @Y+@M+@DSELECT @COUNT = COUNT(CODE) FROM TABLENAME WHERE CODE LIKE @TODAY+'%'
IF(@COUNT<>0)
SELECT @MAX = MAX(CODE)+1 FROM TABLENAME WHERE CODE LIKE @TODAY+'%'
ELSE
@MAX=1SET @MAXSTR = CAST(@MAX AS VARCHAR(5))IF(LEN(@MAXSTR)=1)
SET @MAXSTR = '0000'+ @MAXSTR
IF(LEN(@MAXSTR)=2)
SET @MAXSTR = '000'+ @MAXSTR
IF(LEN(@MAXSTR)=3)
SET @MAXSTR = '00'+ @MAXSTR
IF(LEN(@MAXSTR)=4)
SET @MAXSTR = '0'+ @MAXSTRSELECT @ID = ID FROM INSERTEDUPDATE TABLENAME SET CODE=@TODAY+@MAXSTR WHERE ID=@IDGO
下面这个方法也是实现楼主功能的。
public String loadReportCode(String date) throws Report_codeException{
String strReportCode = "";
ResultSet rs = null;
String strSql = "select max(report_code) as code "
+ "FROM sale "
+ "where mid(report_code,1,8) = ?";
//执行查询
try {
PreparedStatement pst = con.prepareStatement(strSql);
pst.setString(1, date);
rs = pst.executeQuery();
if(rs.next()){
String strCode = rs.getString("code");
if(strCode != null){
strCode = strCode.substring(8,10);//取出最后二位
int temp = Integer.parseInt(strCode);
if(temp>9){
temp += 1;
strReportCode = date.concat(String.valueOf(temp));
}else if(temp == 9){
strReportCode = date.concat("10");
}else{
temp += 1;
strReportCode = date.concat("0").concat(String.valueOf(temp));//参数加0后再加数
}
}
//查询没有记录则在参数年月日20040517后加01
else{
strReportCode = date.concat("01");
}
}
rs.close();
}
catch(Exception e){
e.printStackTrace();
} return strReportCode;
}