自增号

1: 自增列 类型为：int identity(1,1) 当然也可以是bigint,smallint 
eg: create table tbName(id int identity(1,1),description varchar(20)) 
或在用企业管理器设计表字段时，将字段设为int,将标识设为是，其它用默认即可

IDENTITY (给SQL 的 select 语句添加一个自动递增的ID字段)
select id1=identity(int,1,1) , * into #test from tb 
select * from #test

@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定义的全局变量。一般系统定义的全局变量都是以@@开头，用户自定义变量以@开头。比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据后自增列的值自动增加至101，则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后，执行select @@identity的时候连接没有关闭，否则得到的将是NULL值。
 


我的例子：--查询时加子增列（需要用到）
select identity(int,1,1) as id,newid() as Guid,qq into #tp from QQ--（QQ（qq(nvarchar(11))））

select * from #tp

drop table #tp
select @@identity

2: 查询时加序号: 
a:没有主键的情形： (注意语法：Select identity(int,1,1) as iid,* into #tmp from TableName   )
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