[交流]自增号

[txlicenhe]

若有错漏，请指正或补充

[txlicenhe]

大家有好的也往上面贴啊。

[911853]

看看

[zjcxc]

看看.

[txlicenhe]

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

[txlicenhe]

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 ...这些时间改成排班时间就更好了。

[txlicenhe]

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()

[Dennis618]

大家交流一下，如果有空缺会自己填上，自动递增。
select (case when isnull((select min(id) as id from 物品 where 编号=1),0)=0 then 1 else isnull(min(编号),0)+1 end) as ID from 物品 a where not exists(select 1 from 物品 where 编号=a.编号+1)

[lvltt]

收藏 学习

[zarge]

收

[zjcxc]

错误的级别不同,你观察出错提示就知道了.

第一种写法产生的错误是可以忽略的错误,下面的语句可以再执行下去.
这种错误是发生在数据存储进行的时候,发现存储长度问题,这时相当于插入了记录,再回滚.
所以id增加

第一种写法产生的错误是不可以忽略的错误,下面的语句根本不会再执行下去.
这种错误是发生在准备进行数据存储的时候,既然没有产生存储动作,当然id不会增加

[wzh1215]

我在对有自动增长的列进行插入数据时，有时插入失败时，自动编号也会增加，真是不可理解。
如：
create table #aa(id int identity (1,1) not null,field1 int,field2 varchar(10))
insert into #aa values(1,'aaa')
insert into #aa values(2,'bbb')
insert into #aa values(3,'ccccccccccccc')
insert into #aa values(4,'eee')
select * from #aa
drop table #aa
结果为：
id field1 field2
----------- ----------- ----------
1 1 aaa
2 2 bbb
4 4 eee

(3 row(s) affected)

[frogshero]

good,thanks

[zjcxc]

to: wzh1215(四脚蛇)
自增号是这样的.有插入就会增加,不管是否失败

[wzh1215]

那这个呢？
create table #aa(id int identity (1,1) not null,field1 int,field2 varchar(10))
insert into #aa values(1,'aaa')
insert into #aa values(2,'bbb')
insert into #aa values(a,'ccc')
insert into #aa values(4,'eee')
select * from #aa
drop table #aa
结果：
id field1 field2
----------- ----------- ----------
1 1 aaa
2 2 bbb
3 4 eee

(3 row(s) affected)

[txlicenhe]

to: wzh1215(四脚蛇)

第一种写法：如果一起运行，出错后下一句还能运行
第二种写法：如果一起运行，会直接出错，所有语句都没有运行。

[wzh1215]

不是一起运行，
是一条一条的执行的！
同样是失败，失败原因不同导致结果不同！

[txlicenhe]

第一种写法：如果一起运行，出错后下一句还能运行(相当于没有语法错误)
第二种写法：如果一起运行，会直接出错，所有语句都没有运行。(相当于有语法错误)

[owenszc]

极品。。。那1分能不能送个我呀。。呵呵。。开个玩笑

[txlicenhe]

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()

[txlicenhe]

--自已做标识列的例子:

--创建得到最大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