34,590
社区成员
发帖
与我相关
我的任务
分享
--楼上的法子可以,不过如果id断了的话,就不行了。。
create table t(id int identity(1,1),name varchar(10),n int)
go
--创建函数
create function f_t()
returns int
as
begin
declare @n int
select top 1 @n=case when isnull(n,0)=0 or isnull(n,0)=30 then 1 else n+1 end from t WITH(XLOCK,PAGLOCK) order by id desc
if(@@rowcount=0)
set @n=1
return @n
end
go
insert into t(name,n)select 'bbb',dbo.f_t()
select * from t
select number%30+1 from master..spt_values where type='p'
create table tbtest1(fid int identity,id as (fid-1)%30+1,num int)
insert tbtest1
select top 100 id from sysobjects
select id,num from tbtest1
/*
id num
----------- -----------
1 4
2 5
3 7
4 8
5 13
6 15
7 25
8 26
9 27
10 29
11 34
12 41
13 44
14 46
15 49
16 50
17 51
18 54
19 55
20 58
21 60
22 64
23 65
24 67
25 68
26 69
27 71
28 72
29 73
30 74
1 75
2 76
3 78
4 90
5 91
6 92
7 93
8 94
9 95
10 96
11 97
12 18815129
13 32719169
14 48719226
15 52195236
16 62623266
17 64719283
18 80719340
19 96719397
20 112719454
21 116195464
22 128719511
23 130815528
24 132195521
25 132911545
26 144719568
27 160719625
28 176719682
29 192719739
30 208719796
1 224719853
2 240719910
3 251147940
4 256719967
5 260195977
6 272720024
7 276196034
8 279672044
9 288720081
10 292196091
11 304720138
12 320720195
13 322100188
14 324196205
15 326292222
16 336720252
17 340196262
18 352720309
19 356196319
20 368720366
21 372196376
22 384720423
23 388196433
24 400720480
25 404196490
26 416720537
27 418100530
28 420196547
29 427864591
30 432720594
1 436196604
2 439672614
3 448720651
4 452196661
5 455672671
6 459864705
7 464720708
8 468196718
9 471672728
10 475148738
(100 行受影响)
*/
可以创建约束,比如;
create table t(n int check(n>1 and n<30))