给出某个数值,例,1,2,3,4,5
要求显示结果:123
234
345
451
512
--
等某数字和字母自助组合,且无重复数字的语法
解决方案 »
- 难题来了……关于验证15位18位身份证的问题。比较苛刻,希望高手来[最先给予完美答案的送QQ黄钻一个月]
- An INSERT EXEC statement cannot be nested.问题
- 请问如何禁止xp_cmdshell啊
- 请教:sqlserver2k中的字符串连接符号+出现的问题,百思不得其解,急急?
- 求一个where方法,不含用指定关键字
- 请教参数数组问题
- oracle 链接服务器问题!
- 如何获得一个表的最后更新时间?(立即给分)
- 关于SQL SERVER 2000的两个小问题
- 如何自动给表中的数据顺序分配行号?
- 关于运行存储过程 导致temp数据库增加,运算停止的问题。
- SQL server 2008安装时出现VS2008需要升级到SP1的提示?
declare @M varchar(50),
@N xml
set @M='10663,10662,10661,10660'set @N=convert(xml,'<r><c>'+replace(@M,',','</c><c>')+'</c></r>')
select col=T.c.value('.[1]','varchar(20)')
from @N.nodes('/r/c') T(c)/*****************col
--------------------
10663
10662
10661
10660(4 行受影响)
SET NOCOUNT ON
DECLARE @Ints VARCHAR(1000)
DECLARE @Table TABLE(ID INT IDENTITY(1,1),unit VARCHAR(10),choose BIT)
DECLARE @Door_Int INT
DECLARE @Unit VARCHAR(10)
DECLARE @OutputN INT
DECLARE @OutputM INT
DECLARE @Line INT
DECLARE @Rand INT
DECLARE @Result VARCHAR(100) = ''
DECLARE @Num INTSET @Ints = '1,2,3,4,5,A,B,C'
SET @Door_Int = 1
SET @OutputN = 10 --产生随机组合个数
SET @OutputM = 3 --产生随机数位数
SET @Num = 0WHILE @Door_Int > 0
BEGIN
SET @Door_Int = CHARINDEX(',',@Ints)
IF @Door_Int = 0
BEGIN
BREAK
END
SET @Unit = LEFT(@Ints,@Door_Int - 1)
SET @Ints = RIGHT(@Ints,LEN(@Ints) - @Door_Int)
INSERT INTO @Table VALUES (@Unit,0)
ENDSELECT @Line = MAX(ID) FROM @Table
SET @Door_Int = 0WHILE @Door_Int < @OutputN
BEGIN
SET @Num = 0
WHILE @Num < @OutputM
BEGIN
SET @Rand = RAND() * @Line + 1
IF EXISTS(SELECT 1 FROM @Table WHERE ID = @Rand AND choose = 0)
BEGIN
SELECT @Result = @Result + unit FROM @Table WHERE ID = @Rand
UPDATE @Table SET choose = 1 WHERE ID = @Rand
SET @Num = @Num + 1
END
END
SET @Door_Int = @Door_Int + 1
UPDATE @Table SET choose = 0
PRINT @Result
SET @Result = ''
END431
314
214
B34
523
253
532
A1B
452
A52
DECLARE @Ints VARCHAR(1000)
DECLARE @Table TABLE(ID INT IDENTITY(1,1),unit VARCHAR(10),choose BIT)
DECLARE @Door_Int INT
DECLARE @Unit VARCHAR(10)
DECLARE @OutputN INT
DECLARE @OutputM INT
DECLARE @Line INT
DECLARE @Rand INT
DECLARE @Result VARCHAR(100) = ''
DECLARE @Num INTSET @Ints = '1,2,3,4,5,A,B,C'
SET @Door_Int = 1
SET @OutputN = 10 --产生随机组合个数
SET @OutputM = 3 --产生随机数位数
SET @Num = 0WHILE @Door_Int > 0
BEGIN
SET @Door_Int = CHARINDEX(',',@Ints)
IF @Door_Int = 0
BEGIN
INSERT INTO @Table VALUES (@Ints,0)
BREAK
END
SET @Unit = LEFT(@Ints,@Door_Int - 1)
SET @Ints = RIGHT(@Ints,LEN(@Ints) - @Door_Int)
INSERT INTO @Table VALUES (@Unit,0)
ENDSELECT @Line = MAX(ID) FROM @Table
SET @Door_Int = 0WHILE @Door_Int < @OutputN
BEGIN
SET @Num = 0
WHILE @Num < @OutputM
BEGIN
SET @Rand = RAND() * @Line + 1
IF EXISTS(SELECT 1 FROM @Table WHERE ID = @Rand AND choose = 0)
BEGIN
SELECT @Result = @Result + unit FROM @Table WHERE ID = @Rand
UPDATE @Table SET choose = 1 WHERE ID = @Rand
SET @Num = @Num + 1
END
END
SET @Door_Int = @Door_Int + 1
UPDATE @Table SET choose = 0
PRINT @Result
SET @Result = ''
END
CREATE TABLE #(ID int)INSERT INTO #
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5SELECT LTRIM(A.ID)+LTRIM(B.ID)+LTRIM(C.ID)
FROM # A,# B, # C
WHERE A.id<>B.id
AND B.id<>C.id
AND A.id<>C.id
ORDER BY
LTRIM(A.ID)+LTRIM(B.ID)+LTRIM(C.ID)/*
123
124
125
132
134
135
142
143
145
152
153
154
213
214
215
231
234
235
241
243
245
251
253
254
312
314
315
321
324
325
341
342
345
351
352
354
412
413
415
421
423
425
431
432
435
451
452
453
512
513
514
521
523
524
531
532
534
541
542
543
*/