sql自增号

1:   自增列       类型为：int   identity(1,1)     当然也可以是bigint,smallint    
        eg:   create   table   tbName(id   int   identity(1,1),description   varchar(20))  
        或在用企业管理器设计表字段时，将字段设为int,将标识设为是，其它用默认即可  
   
  2:   查询时加序号:  
      a:没有主键的情形：  
        Select   identity(int,1,1)   as   iid,*   into   #tmp   from   TableName  
        Select   *   from   #tmp  
        Drop   table   #tmp  
      b:有主键的情形：  
        Select   (Select   sum(1)   from   TableName   where   KeyField   <=   a.KeyField)   as   iid,*   from   TableName   a  
3:生成自增序列号的表  
      eg:   生成一列0-30的数  
        Select   top   30   (select   sum(1)   from   sysobjects   where   name<=   a.name)-1   as   id   from   sysobjects   a  
   
      当然，可能sysobjects   中没有这么多条记录，比如只有100条，我需生成1-800的序列号  
      如下处理：  
  Select   (Select   sum(1)   from   (Select   top   800   a.name   as   name1,b.name   as   name2   from   sysobjects   a   ,sysobjects   b)   cc   where   name1<=   dd.name1   and   name2   <=   dd.name2   )   from    
  (Select   top   800   a.name   as   name1,b.name   as   name2   from   sysobjects   a   ,sysobjects   b)   dd  
   
   
  应用举例  
  eg1:    
  create   table   t(日期   char(8),请假人数   int)  
  insert   t   select   '20031001',3  
  Union   all   select   '20031003',2  
  Union   all   select   '20031004',1  
  Union   all   select   '30031031',5  
  要列出2003年10月每一天的请假人数，若没有，以0表示。  
   
  Select   convert(char(8),dateadd(day,id,'20031001'),112),IsNull(t.请假人数,0)   from    
  (Select   top   31   (select   sum(1)   from   sysobjects   where   name<=   a.name)-1   as   id   from   sysobjects   a)   bb  
  left   join   t   on   convert(char(8),dateadd(day,id,'20031001'),112)   =   t.日期  
   
   
   
  eg2: 生成随机考勤打卡资料：  
   
  declare   @r   int  
  --得到要处理的记录数  
  set   @r=900  
   
  --创建得到随机时间的临时表  
  create   table   #tb(id   int   identity(1,1),dt1   datetime,dt2   datetime,dt3   datetime,dt4   datetime,dt5   datetime,dt6   datetime)  
   
  --生成随机时间  
  declare   @sql   varchar(8000)  
  set   @sql='insert   into   #tb(dt1,dt2,dt3,dt4,dt5,dt6)   select   top   '+cast(@r   as   varchar)+'  
    dateadd(ss,rand(a.id)*1800,''07:30''),  
    dateadd(ss,rand(a.id+1)*400,''11:30''),  
    dateadd(ss,rand(a.id+2)*1600,''13:00''),  
    dateadd(ss,rand(a.id+3)*300,''17:30''),  
    dateadd(ss,rand(a.id+4)*800,''17:45''),  
    dateadd(ss,rand(a.id+5)*250,''20:00'')  
    from(select   top   100   id   from   sysobjects)   a,  
  (select   top   9   id   from   sysobjects)   b  
  order   by   newid()  
  exec(@sql)  
  当然，如果将07:30   11:30   ...这些时间改成排班时间就更好了。  
4:   好多单号都是自动增长，但又不能用自增列代替  
        eg:     P031106001       --   第一位P表示采购单,031106表示日期，后三位是流水号。　  
      　如下处理：(编号规则不同时稍加修改即可)  
   
  先建一个自定义函数  
  create   function   getDH()  
  returns   char(10)  
  As  
  begin  
          declare   @dh1   char(10),@dh2   char(10)  
          select   @dh1   =   max(dh)   from     tableName    
          Set   @dh1   =   IsNull(@dh1,'P000000000')  
          set   @dh2   =   Left(@dh1,1)   +   right(convert(varchar(8),getdate(),112),6)   +   '001'  
          if   @dh1   >=   @dh2  
          begin  
                  set   @dh2   =   left(@dh1,7)   +   right('000'+   cast(cast(right(@dh1,3)   as   int)+1   as   varchar),3)  
          end          
          return(@dh2)  
  end  
   
  /********  
        Usage:   select   dbo.getdh()  
  *******/  
   
  然后在字段默认值中填入   dbo.getdh()


 4:   好多单号都是自动增长，但又不能用自增列代替  
        eg:     P031106001       --   第一位P表示采购单,031106表示日期，后三位是流水号。　  
      　如下处理：(编号规则不同时稍加修改即可)  
   
  因在自定义函数内不能用getdate(),先建一个视图  
  create   view   vGetdate  
  as  
      select   getdate()   as   today  
   
   
  先建一个自定义函数  
  create   function   getDH()  
  returns   char(10)  
  As  
  begin  
          declare   @dh1   char(10),@dh2   char(10)  
          select   @dh1   =   max(dh)   from     tableName    
          Set   @dh1   =   IsNull(@dh1,'P000000000')  
          select   @dh2   =   Left(@dh1,1)   +   right(convert(varchar(8),today,112),6)   +   '001'   from   vGetdate  
          if   @dh1   >=   @dh2  
          begin  
                  set   @dh2   =   left(@dh1,7)   +   right('000'+   cast(cast(right(@dh1,3)   as   int)+1   as   varchar),3)  
          end          
          return(@dh2)  
  end  
   
  /********  
        Usage:   select   dbo.getdh()  
  *******/  
   
  然后在字段默认值中填入   dbo.getdh()  
--自已做标识列的例子:  
   
  --创建得到最大id的函数  
  create   function   f_getid()  
  returns   int  
  as  
  begin  
  declare   @id   int  
  select   @id=max(id)   from   tb  
  set   @id=isnull(@id,0)+1  
  return(@id)  
  end  
  go  
   
  --创建表  
  create   table   tb(id   int   default   dbo.f_getid(),name   varchar(10))  
  go  
   
  --创建触发器,在删除表中的记录时,自动更新记录的id  
  create   trigger   t_delete   on   tb  
  AFTER   delete  
  as  
  declare   @id   int,@mid   int  
  select   @mid=min(id),@id=@mid-1   from   deleted  
  update   tb   set   id=@id,@id=@id+1   where   id>@mid  
  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  
   
  --删除环境  
  drop   table   tb  
  drop   function   f_getid