关于sql随机数问题

[foxkla]

我想使一个主键字段 每插入一条数据 就随机生成一个数 就像标示列那样
比如说插入银行用户信息 就自动生成一个银行卡号
请问下大家怎么实现

[华夏小卒]

rand()*N

[--小F--]

--try
select * from tb where 银行卡号=newid()

[pt1314917]

--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
	RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO

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

--插入资料

	INSERT tb(col) VALUES(1)
	INSERT tb(col) VALUES(2)
	INSERT tb(col) VALUES(3)
	DELETE tb WHERE col=3
	INSERT tb(col) VALUES(4)
	INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)


--显示结果
SELECT * FROM tb

[htl258_Tony]

If not object_id('[tb]') is null
	Drop table [tb]
Go
Create table [tb]([姓名] nvarchar(1),[卡号] as abs(checksum(newid()))%1000000)
Insert tb
Select 'a' union all
select 'b'

select * from tb
/*
姓名   卡号
---- -----------
a    390639
b    821254

(2 行受影响)
*/

类似这样子

[foxkla]

[Quote=引用 1 楼 js_szy 的回复:]
rand()*N
[/Quote]
问下这个函数写在哪里呢 插入 还是约束？ 怎么写呢

[让你望见影子的墙]

create table TimeSquare_Code(id bigint identity(1,1) PRIMARY KEY CLUSTERED,code bigint,status int default 0)
go
create procedure sp_TimeSquare_randomcode
as
insert into timesquare_Code(code)
select top 1000000 code=900000000000*RAND(CHECKSUM(NEWID()))[phone] from syscolumns a,syscolumns b,syscolumns c
go
exec sp_timesquare_randomcode
go
select * from timesquare_code
go
drop table timesquare_code
drop procedure sp_TimeSquare_randomcode

[feixianxxx]

SELECT * FROM NORTHWIND..ORDERS ORDER BY NEWID()
--随机排序
SELECT TOP 10 * FROM NORTHWIND..ORDERS ORDER BY NEWID()
--从ORDERS 表中随机取出10 条记录
示例
A.对变量使用NEWID 函数
以下示例使用NEWID() 对声明为UNIQUEIDENTIFIER 数据类型的变量赋值。在测
试UNIQUEIDENTIFIER 数据类型变量的值之前，先输出该值。
-- CREATING A LOCAL VARIABLE WITH DECLARESET SYNTAX.
DECLARE @MYID UNIQUEIDENTIFIER
SET @MYID = NEWID()
PRINT 'VALUE OF @MYID IS '+ CONVERT(VARCHAR(255), @MYID)
下面是结果集：
VALUE OF @MYID IS 6F9619FF-8B86-D011-B42D-00C04FC964FF
注意：
NEWID 对每台计算机前往的值各不相同。所显示的数字仅起解释说明的作用。
随机函数：RAND()
在查询分析器中执行：SELECT RAND()，可以看到结果会是类似于这样的随机小数：
0.36361513486289558，像这样的小数在实际应用中用得不多，一般要取随机数都会取随机
整数。那就看下面的两种随机取整数的方法：
1、
A：SELECT FLOOR(RAND()*N) ---生成的数是这样的：12.0
B：SELECT CAST( FLOOR(RAND()*N) AS INT) ---生成的数是这样的：12
2、
A：SELECT CEILING(RAND() * N) ---生成的数是这样的：12.0
B：SELECT CAST(CEILING(RAND() * N) AS INT) ---生成的数是这样的：12
其中里面的N 是一个你指定的整数，如100，可以看出，两种方法的A 方法是带有.0
这个的小数的，而B 方法就是真正的整数了。
大致一看，这两种方法没什么区别，真的没区别？其实是有一点的，那就是他们的生成随机
数的范围：
方法1 的数字范围：0 至N-1 之间，如CAST( FLOOR(RAND()*100) AS INT)就会生成0
至99 之间任一整数
方法2 的数字范围：1 至N 之间，如CAST(CEILING(RAND() * 100) AS INT)就会生成1
至100 之间任一整数
对于这个区别，看SQL 的联机帮助就知了：
比较CEILING 和FLOOR
CEILING 函数前往大于或等于所给数字表达式的最小整数。FLOOR 函数前往小于或
等于所给数字表达式的最大整数。例如，对于数字表达式12.9273，CEILING 将前往13，
FLOOR 将前往12。FLOOR 和CEILING 前往值的数据类型都与输入的数字表达式的数
据类型相同。
现在，各位就可以根据自己需要使用这两种方法来取得随机数了^_^
另外，还要提示一下各位菜鸟，关于随机取得表中任意N 条记录的方法，很简单，就
用NEWID()：
SELECT TOP N * FROM TABLE_NAME ORDER BY NEWID() ----N 是一个你指定的整
数，表是取得记录的条数.
举例：
经过测试发现：
if object_id('tb') is not null
drop table tb
create table tb (s_id int,t_id int, fenshu int)
insert into tb
select 1,1,66 union all
select 1,2,67 union all
select 2,1,65 union all
select 2,2,78 union all
select 3,1,66 union all
select 3,2,55
--这个方法可以给不同数加上不同随机数（newid（ ））
select fenshu,(fenshu+cast(ceiling(RAND(CHECKSUM(NEWID()))*10)as int))as fenshu
from tb
--这个方法只能给不同数随机加上相同数（RAND（ ））
select fenshu,(fenshu+cast(CEILING(RAND() * 10) AS INT))as fenshu
from tb
或者
SELECT fenshu,fenshu+cast(ceiling(RAND(CHECKSUM(rand()))*10) as int) fenshu2
FROM tb
/*------------
66 68
67 74
65 75
78 80
66 73
55 58
66 68
67 69
65 67
78 80
66 68
55 57
------------*/

[htl258_Tony]

If not object_id('[tb]') is null
	Drop table [tb]
Go
Create table [tb]([姓名] nvarchar(1),[卡号] as '4367'+right(10000+abs(checksum(newid()))%1000000,5)+right(10000+abs(checksum(newid()))%100000,5)+right(10000+abs(checksum(newid()))%100000,5))
Insert tb
Select 'a' union all
select 'b'

select * from tb
/*
姓名   卡号
---- ----------------------------------
a    4367868586561891827
b    4367830075848086421

(2 行受影响)
*/

[soft_wsx]

create view v_RAND
as
select re=stuff(rand(),1,2,'')
go

create function f_RANDBH(@BHLen int)
returns varchar(50)
as
begin
 declare @r varchar(50)
 if not(isnull(@BHLen,0) between 1 and 50)
  set @BHLen=10
lb_bh:
 select @r=re from v_RAND
 while len(@r)<@BHLen
  select @r=@r+re from v_RAND
 set @r=left(@r,@BHLen)

 
 if exists(select * from table1 with(xlock,paglock) where bh=@r)
  goto lb_bh

 return(@r)
end
go
select dbo.f_RANDBH(50)  --位数自定，最长50  --28717932268756598246257824810881924833466287969484

[xiequan2]

--以下代码生成的编号长度为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)
    SELECT @dt=dt FROM v_GetDate
    RETURN(
        SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) 
        FROM tb WITH(XLOCK,PAGLOCK)
        WHERE BH like @dt+'%')
END
GO

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

--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)

--显示结果
SELECT * FROM tb
/*--结果
BH           col 
------------------- ----------- 
050405000001  1
050405000002  2
050405000003  4
050405000004  14
--*/

[fwacky]

---主键 随机，要用 newid（） 

[soft_wsx]

create view v_RAND
as
select re=stuff(rand(),1,2,'')
go

alter function f_RANDBH()
returns varchar(50)
as
begin
declare @bhlen int
set @bhlen=18  --长度
 declare @r varchar(50)
 if not(isnull(@BHLen,0) between 1 and 50)
  set @BHLen=10
lb_bh:
 select @r=re from v_RAND
 while len(@r)<@BHLen
  select @r=@r+re from v_RAND
 set @r=left(@r,@BHLen)

 
 if exists(select * from tb with(xlock,paglock) where bh=@r)
  goto lb_bh

 return(@r)
end
go
select dbo.f_RANDBH(50)  --位数自定，最长50  --28717932268756598246257824810881924833466287969484

if OBJECT_ID('tb') is not null drop table tb
CREATE TABLE tb(
BH varchar(50) PRIMARY KEY DEFAULT dbo.f_RANDBH(),
col int)

insert tb(col) select '1'
union all select '2'
union all select '3'
union all select '4'

select * from tb

/*
BH	col
174278935021669799	2
414909187508641261	1
833422059086275920	4
978033706486577366	3
*/

[foxkla]

谢谢各位了 都这么强....... 小小分数不以为报
我得慢慢消化消化

[黄_瓜]

转 zjcxc

最近在论坛看到有人问，如何快速生成100万不重复的8位编号，对于这个问题，有几点是需要注意的：
1．    如何生成8位随机数，生成的数越随机，重复的可能性当然越小
2．    控制不重复
3．    考虑性能
针对这个问题，我写了如下的示例来解决，希望能为有这类需求的人提供指导


生成100万条8位不重复数据的示例

USE tempdb
GO

-- 创建测试表

CREATE TABLE tb(id char(8))

-- 创建用于自动过滤重复值的唯一索引

CREATE UNIQUE INDEX IX_tb ON tb(id)
WITH IGNORE_DUP_KEY 
GO

-- 测试数据插入的处理时间, 记录开始处理的时间点

DECLARE @dt datetime
SET @dt = GETDATE()
-- 插入随机数据

SET NOCOUNT ON

DECLARE @row int
SET @row = 1000000  -- 设置总记录数

WHILE @row >0
BEGIN
    -- 显示提示信息, 表示还需要插入多行数据
    RAISERROR('need %d rows', 10, 1, @row) WITH NOWAIT
    -- 插入随机的位编码数据

    SET ROWCOUNT @row

    INSERT tb SELECT
        id = RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)
    FROM syscolumns c1, syscolumns c2

    SET @row = @row - @@ROWCOUNT
END

-- 显示插入数据使用的时间
SELECT BeginDate = @dt, EndDate = GETDATE(), 
    Second = DATEDIFF(Second, @dt, GETDATE()),
GO
-- 显示最终的结果记录是否正确
SELECT COUNT(*) FROM tb
GO
-- 删除测试
DROP TABLE tb



解决中用到的技巧：

1．    控制产生的数据不重复，直接使用唯一索引中的 IGNORE_DUP_KEY 选项，使插入数据中的重复值自动过滤，避免手工处理重复

2．    使用 CHECKSUM 配合 NEWID() 函数，使生成的数据尽量随机，一般生成随机数会考虑使用 RAND() 函数，但这个函数是产生伪随机值，
用下面的语句测试一下，会发现产生的数据全部是一样的，这不适用于想批量生成多个随机数，而NEWID() 函数生成的是GUID，基本上不会有重复的，
再通过CHECKSUM将其转化成数字，这样产生重复的可能性会比较小

[billrobin]

百万记录