如何用SQL生成随机ID

[Mahes]

我的table中有ClientID as primal key，Clientname，Address.. 现在想每次添加新的client的时候，ID自动生成，定成长的6位数,请教各位，该如何做

[wwd252]

[Quote=引用 6 楼 xiaoku 的回复:]
引用 5 楼 Garnett_KG 的回复:
梁MM，你的百宝箱里到底有多少东东.
[/Quote]

[liangCK]

--取得随机数的视图
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

	SELECT @r=CHAR(
			CASE WHEN SUBSTRING(re,1,1)>5 THEN 97 ELSE 65 end
			+(SUBSTRING(re,1,1)
			+SUBSTRING(re,2,1)
			+SUBSTRING(re,3,1))%26)
		+CHAR(
			CASE WHEN SUBSTRING(re,4,1)>5 THEN 97 ELSE 65 end
			+(SUBSTRING(re,4,1)
			+SUBSTRING(re,5,1)
			+SUBSTRING(re,6,1))%26)
	FROM v_RAND
	WHILE LEN(@r)<@BHLen
		SELECT @r=@r+CHAR(
				CASE WHEN SUBSTRING(re,1,1)>5 THEN 97 ELSE 65 end
				+(SUBSTRING(re,1,1)
				+SUBSTRING(re,2,1)
				+SUBSTRING(re,3,1))%26)
			+CHAR(
				CASE WHEN SUBSTRING(re,4,1)>5 THEN 97 ELSE 65 end
				+(SUBSTRING(re,4,1)
				+SUBSTRING(re,5,1)
				+SUBSTRING(re,6,1))%26)
		FROM v_RAND
	RETURN(LEFT(@r,@BHLen))
END
GO

--调用
SELECT dbo.f_RANDBH(6),dbo.f_RANDBH(8)
--结果: YZVBOj   LASCrhSO

[liangCK]

--取得随机数的视图
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 tb WITH(XLOCK,PAGLOCK) WHERE BH=@r)
		GOTO lb_bh

	RETURN(@r)
END
GO

--创建引用生成随机编号的函数
CREATE TABLE tb(
BH char(10) PRIMARY KEY DEFAULT dbo.f_RANDBH(10),
col int)

--插放数据
BEGIN TRAN
	INSERT tb(col) VALUES(1)
	INSERT tb(col) VALUES(2)
	INSERT tb(col) VALUES(3)
COMMIT TRAN
SELECT * FROM tb
GO
/*--结果
BH                   col 
------------------------------ ----------- 
6128177354           1
7378536177           3
8387186129           2
--*/

[liangCK]

--取得随机数的视图
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

	SELECT @r=CHAR(65
			+(SUBSTRING(re,1,1)
			+SUBSTRING(re,2,1)
			+SUBSTRING(re,3,1))%26)
		+CHAR(65
			+(SUBSTRING(re,4,1)
			+SUBSTRING(re,5,1)
			+SUBSTRING(re,6,1))%26)
	FROM v_RAND
	WHILE LEN(@r)<@BHLen
		SELECT @r=@r+CHAR(65
				+(SUBSTRING(re,1,1)
				+SUBSTRING(re,2,1)
				+SUBSTRING(re,3,1))%26)
			+CHAR(65
				+(SUBSTRING(re,4,1)
				+SUBSTRING(re,5,1)
				+SUBSTRING(re,6,1))%26)
		FROM v_RAND
	RETURN(LEFT(@r,@BHLen))
END
GO

--调用
SELECT dbo.f_RANDBH(6),dbo.f_RANDBH(8)
--结果: UJXIJD  PAPGTQUX

[Mahes]

多谢多谢

[Garnett_KG]

梁MM，你的百宝箱里到底有多少东东.

[xiaoku]

[Quote=引用 5 楼 Garnett_KG 的回复:]
梁MM，你的百宝箱里到底有多少东东.
[/Quote]