declare @rtn as varchar(12) if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112)) set @rtn = select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112) else set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'print @rtn
create table tb(col varchar(12)) insert into tb values('S20090323001') insert into tb values('S20090323002') godeclare @rtn as varchar(12) if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112)) set @rtn = (select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112)) else set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'print @rtndrop table tb/* s20090323003 */--将系统时间更改为2009-03-24,再次运行. create table tb(col varchar(12)) insert into tb values('S20090323001') insert into tb values('S20090323002') godeclare @rtn as varchar(12) if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112)) set @rtn = (select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112)) else set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'print @rtndrop table tb/* s20090324001 */
create table tb(col varchar(12)) insert into tb values('S20090323001') insert into tb values('S20090323002') goDECLARE @currentMaxV VARCHAR(12),@theValue VARCHAR(12) SELECT @currentMaxV=MAX(col) FROM tb WHERE col LIKE 'S' + CONVERT(VARCHAR(10),GETDATE(),112) + '[0-9][0-9][0-9]' SELECT @theValue = CASE WHEN @currentMaxV IS NULL THEN 'S' + CONVERT(VARCHAR(10),GETDATE(),112) + '001' ELSE LEFT(@currentMaxV,9) + RIGHT(1000 + RIGHT(@currentMaxV,3) * 1+1,3) END SELECT @theValue /* S20090324001 */GODROP TABLE tb GO
不一定非要用sql生成吧 自己可以写一个静态变量来保存后面的序列号, 写一个方法来构造这个字符串,根据日期的不同序列号每次从0开始, static long sequence_=1; static long last_date; public String getString(){ if(last_date<>now_date){ sequence_=1; } //format 一下sequence_,如果不足三位前面补0 String returnValue="s"+now_date+sequence_; sequence_++; } 最后在jsp页面上调用这个方法就行了。。
create trigger trig_insert_students on student for insert as update a set 学号='s'+convert(varchar(8),getdate(),112)+right('000'+rtrim(isnull((select max(right(学号,3)) from student where left(学号,9)='s'+convert(varchar(8),getdate(),112)),'000')+1),3) from student a, inserted b where a.姓名=b.姓名 and a.性别=b.性别 and a.籍贯=b.籍贯
if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
set @rtn = select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112)
else
set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'print @rtn
insert into tb values('S20090323001')
insert into tb values('S20090323002')
godeclare @rtn as varchar(12)
if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
set @rtn = (select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
else
set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'print @rtndrop table tb/*
s20090323003
*/--将系统时间更改为2009-03-24,再次运行.
create table tb(col varchar(12))
insert into tb values('S20090323001')
insert into tb values('S20090323002')
godeclare @rtn as varchar(12)
if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
set @rtn = (select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
else
set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'print @rtndrop table tb/*
s20090324001
*/
insert into tb values('S20090323001')
insert into tb values('S20090323002')
goDECLARE @currentMaxV VARCHAR(12),@theValue VARCHAR(12)
SELECT @currentMaxV=MAX(col) FROM tb WHERE col LIKE 'S' + CONVERT(VARCHAR(10),GETDATE(),112) + '[0-9][0-9][0-9]'
SELECT @theValue = CASE WHEN @currentMaxV IS NULL THEN 'S' + CONVERT(VARCHAR(10),GETDATE(),112) + '001' ELSE LEFT(@currentMaxV,9) + RIGHT(1000 + RIGHT(@currentMaxV,3) * 1+1,3) END
SELECT @theValue
/*
S20090324001
*/GODROP TABLE tb
GO
自己可以写一个静态变量来保存后面的序列号,
写一个方法来构造这个字符串,根据日期的不同序列号每次从0开始,
static long sequence_=1;
static long last_date;
public String getString(){
if(last_date<>now_date){
sequence_=1;
}
//format 一下sequence_,如果不足三位前面补0
String returnValue="s"+now_date+sequence_;
sequence_++;
}
最后在jsp页面上调用这个方法就行了。。
有以下几个属性:学号、姓名、性别、籍贯
就是想自动生成学号,如s20090324001、s20090324002之类,怎样插进呢?
for insert
as
update a set 学号='s'+convert(varchar(8),getdate(),112)+right('000'+rtrim(isnull((select max(right(学号,3)) from student where left(学号,9)='s'+convert(varchar(8),getdate(),112)),'000')+1),3)
from student a, inserted b
where a.姓名=b.姓名 and a.性别=b.性别 and a.籍贯=b.籍贯
--只需要插入这3个值就可以了,在插入以后,学号自动生成。
insert student(姓名,性别,籍贯) values()
<%
int year,month,day,hour,minute,second;
String appno,sy1="",sy2="",appno2;
GregorianCalendar calendar;
calendar = new GregorianCalendar();
year = calendar.get(Calendar.YEAR);
month = calendar.get(Calendar.MONTH)+1;
day = calendar.get(Calendar.DAY_OF_MONTH);
hour = calendar.get(Calendar.HOUR_OF_DAY);
minute = calendar.get(Calendar.MINUTE);
second = calendar.get(Calendar.SECOND);
if( month < 10)
{sy1 = "0"+month;}
else
{sy1 = ""+month;}
if( day <= 9)
{sy2 = "0"+day;}
else
{sy2 = ""+day;}
appno = "LR"+year +sy1+ sy2;
......省阅
strSQL = "SELECT count(*) as recordcount FROM rohsitem WHERE app_no like '"+appno+"%'";
rs = stmt.executeQuery(strSQL);
if (rs.next())
totalrecord = rs.getInt("recordcount"); if(totalrecord ==0)
appno2 = appno +"01";
else if(totalrecord <=9)
appno2 = appno +"0"+(totalrecord+1);
else
appno2 = appno +(totalrecord+1);
%>