随时间增长从头记数.txt

SQL语言常用的一些命令各代码

/*创建表*/
create table tab1(a int ,b char(1),c char(10))


--解法2：
/*建立触发器*/
create trigger autoadd 
on tab1
for insert
as
declare @autoid int,@b char(1),@date char(10)
select @autoid=1,
       @date=(select convert(char(10),getdate(),121))

select @autoid=(select isnull(max(a),0)+1 from tab1 where c=@date)
select @b=(select  min(b) from inserted)
while @autoid<=(select count(0) from tab1 where c=@date)+(select count(0) from inserted)
begin
update tab1 set 
   a= @autoid ,
   c=@date
where b=@b
select @autoid=@autoid+1
select @b=(select min(b) from inserted where b>@b)
end
--插入测试
insert into tab1(b) select 'q'
union all        select 'w'
union all        select 'e'

select * from tab1 order by c,a
truncate table tab1
insert into tab1 select 1,'x','2006-09-10'

--解法2：

/*建立存储过程*/
create proc insert_add 
 @b char(1)
as
declare @date char(10),
        @a int 
select  @date=(select convert(char(10),getdate(),121))
select  @a=(select count(0) from tab1 where c=@date)+1
insert into tab1 select @a,@b,@date
/*运行*/
insert_add 'x'
insert_add 'a'