27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TB TABLE (ID INT)
DECLARE @I INT,@A INT
SET @I=100
WHILE @I>0
BEGIN
SELECT @A=LEFT(RAND()*1000000,4)
IF NOT EXISTS( SELECT 1 FROM @TB WHERE ID=@A)
BEGIN
INSERT INTO @TB SELECT @A
SET @I=@I-1
END
END
if object_id('tb') is not null
drop table tb
go
create table tb(num int)
go
insert tb
select distinct top 1000 abs(checksum(newid()))%9000+1000 from sysobjects,syscolumns
DECLARE @TB TABLE (ID INT)
DECLARE @I INT,@A INT
SET @I=100
WHILE @I>0
BEGIN
SELECT @A=LEFT(RAND()*1000000,4)
IF NOT EXISTS( SELECT 1 FROM @TB WHERE ID=@A)
BEGIN
INSERT INTO @TB SELECT @A WHERE NOT EXISTS (SELECT 1 FROM @TB WHERE ID=@A)
SET @I=@I-1
END
END
SELECT * FROM @TB
/*
ID
-----------
2342
1679
1620
8661
9038
8816
7149
8050
2430
2629
9865
6065
1064
1271
5012
1892
2269
3576
2150
2955
4762
8650
8312
2524
2122
7707
6820
9688
5062
7395
6552
9643
7222
8078
1399
8667
9612
5030
1210
6632
9067
8376
1434
4572
7947
8340
1289
9569
5437
3506
7776
5373
2673
8298
9159
2603
4741
8523
7873
5739
1815
1816
4071
7473
8911
4800
7949
9291
2749
7113
2825
1503
9182
9280
6305
7503
6870
2557
9520
3158
5853
1074
4244
3310
8773
5877
6896
1648
5186
9650
4479
8522
1227
1865
8375
5882
7226
9135
4236
8171
(所影响的行数为 100 行)
*/
if object_id('tb') is not null
drop table tb
go
create table tb(num int)
declare @i int,@num int
set @i=100
while @i>0
begin
set @num = abs(checksum(newid()))%9000+1000
if not exists(select 1 from tb where num=@num)
insert into tb select @num
set @i=@i-1
end
select * from tb order by num