获取自动编号的问题(经典实用)

[lianchenlong000]

我现在不想用自动编号了,能不能写一个存储过程或者函数,实现怎样的功能呢?
我指定一个数据表名称,指定一个最后一条记录的编号,然后获取到新的编号.
比如:t_user(id int,name varchar(20)) 里边有 1 2 3 4 5 7 9 七条记录,
我现在想要的效果就是能返回6,也就是说,能将中间空余的编号利用起来,这样好不好实现?

[ai_li7758521]

感觉这么做没必要，id用bigint：
-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)
随便用，一般是用不完的。

[ai_li7758521]

其实一般的用int也够了
int ： -2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)

[让你望见影子的墙]

--1．融合了补号处理的流水号编号处理
--下面是使用补号法生成流水编号的用户定义函数，生成的编号总长度为8位，以BH开头，其余6位为流水号。

--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
	DECLARE @r char(8)
	
	SELECT @r='BH'+RIGHT(1000001+MIN(BH),6)
	FROM(
		SELECT BH=RIGHT(BH,6) FROM tb WITH(XLOCK,PAGLOCK)
		UNION ALL SELECT 0
	)a WHERE NOT EXISTS(
		SELECT * FROM tb  WITH(XLOCK,PAGLOCK)
		WHERE BH='BH'+RIGHT(1000001+a.BH,6))
	RETURN(@r)
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
GO


/*==================================================*/



--2．融合了补号处理的日期编号处理
--下面是使用补号法生成日期编号的用户定义函数，生成的编号总长度为12，前6位为日期信息，格式为YYMMDD，后6位为流水号。

--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO

--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
	DECLARE @dt CHAR(6),@r char(12)
	SELECT @dt=dt FROM v_GetDate

	SELECT @r=@dt+RIGHT(1000001+MIN(BH),6)
	FROM(
		SELECT BH=RIGHT(BH,6) FROM tb WITH(XLOCK,PAGLOCK)
		WHERE BH like @dt+'%'
		UNION ALL SELECT 0
	)a WHERE NOT EXISTS(
		SELECT * FROM tb  WITH(XLOCK,PAGLOCK)
		WHERE BH like @dt+'%'
			AND BH=@dt+RIGHT(1000001+a.BH,6))
	RETURN(@r)
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

[zhengduan964532]

if object_id('tab_eg3')is not null
   drop table tab_eg3
create table tab_eg3
(id int,
 name varchar(20)
  )
insert into tab_eg3
select '1','小名' union all
select '2','张三' union all
select '3','李四' union all
select '4','王五' union all
select '5','小小' union all
select '7','大大' union all
select '9','大小' 

select id=identity(int,1,1) ,name into #tab_eg3 from tab_eg3
select * from #tab_eg3
select id from #tab_eg3 a where id not in (select id from tab_eg3)

[人鱼传说]

楼主的需要看来去合理,但实际上是有问题的

[Zoezs]

构造一张ID连续的临时表，将实际表中的id字段与临时表中的ID字段比较，吧实际表中没有的取出来就行了。

[jiangshun]

水哥写的

SELECT * INTO # FROM TB
GO
TRUNCATE TABLE TB
GO
INSERT TB(字段列表) SELECT 字段列表 FROM #　ORDER BY ID 
GO
DROP TABLE #




DECLARE @T TABLE(ID INT IDENTITY,[NAME] VARCHAR(10))
INSERT @T SELECT 'A'
INSERT @T SELECT 'B'
DELETE @T WHERE ID=2
INSERT @T SELECT 'C'
INSERT @T SELECT 'D'
SELECT * FROM @T
SELECT ID=(SELECT COUNT(*) FROM @T WHERE ID<=T.ID),[NAME] FROM @T T
/*ID          NAME       
----------- ---------- 
1           A
3           C
4           D

(影響 3 個資料列)

ID          NAME       
----------- ---------- 
1           A
2           C
3           D
*/

[Zoezs]

select number as id into #temp from master.dbo.spt_values where type='p' and number<1000
select id from #temp a where not exists (select top 1 0 from t_user where id=a.id)

[Zoezs]

if object_id('t_user')is not null
   drop table t_user
create table t_user
(id int,
 name varchar(20)
  )
insert into t_user
select '1','小名' union all
select '2','张三' union all
select '3','李四' union all
select '4','王五' union all
select '5','小小' union all
select '7','大大' union all
select '9','大小' 

select number as id into #temp from master.dbo.spt_values where type='p' and number<1000
select id from #temp a where id<100 and not exists (select top 1 0 from t_user where id=a.id)

100以内没用过的ID全部出来了。
0
6
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

[jimwoo]

create table tab_eg3
(id int,
 name varchar(20)
  )
insert into tab_eg3
select '1','小名' union all
select '2','张三' union all
select '3','李四' union all
select '4','王五' union all
select '5','小小' union all
select '7','大大' union all
select '9','大小' 


select min(a.id) + 1 as id from tab_eg3 a left join tab_eg3 b on a.id + 1 = b.id where b.id is null

drop table tab_eg3
/*
id          
----------- 
6
*/

[xjlqlqlq]

介绍个新算法,仅供参考:
由于ID一般都是数字,也就是说,是可以排序的,所以,可以用排序算法找.
1.先查出已有ID排序.
2.用快速算法找,假定用(向上)对半查找法,很快就能递归找到最小的空号(行号<ID).
(当然有更好的算法更好).


另外,其实也有另一种高效算法可参考:
定期一次把空号都找出,存放到一个空号表中(没有空号也可以一次把一批新号存入表中),这样,每次就只管从该表从小到大取号,取完后,在批次存入一批. 这样的批量号法,应该是综合效率比较高(一次新批量的多少,看自己具体业务的使用量情况).

[lwxcy1]

select top 1 id from test a
where ( select id from test b where b.id=a.id+1) is null

[yingzhilian2008]

declare @id int
set @id=1
--select * from employee where employeeid=@id
while(1=1)
begin
   select * from employee where employeeid=@id
   if(@@rowcount=0)
		begin
			break
		end
   else
		begin
		  set @id=@id+1
		end
end

select @id

取到你要的值也就是你说的6

[lnls9999]

学习学习学习