日期:2014-05-18  浏览次数:20376 次

SQL 某数字和字母自助组合,且无重复数字的语法

给出某个数值,例,1,2,3,4,5


要求显示结果:

123
234
345
451
512
--


某数字和字母自助组合,且无重复数字的语法

------解决方案--------------------
SQL code

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 行受影响)

------解决方案--------------------
SQL code

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 INT

SET @Ints = '1,2,3,4,5,A,B,C'
SET @Door_Int = 1
SET @OutputN = 10       --产生随机组合个数
SET @OutputM = 3        --产生随机数位数
SET @Num = 0

WHILE @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)
    
END

SELECT @Line = MAX(ID) FROM @Table
SET @Door_Int = 0

WHILE @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

431
314
214
B34
523
253
532
A1B
452
A52

------解决方案--------------------
探讨

SQL code

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
……

------解决方案--------------------
SQL code

CREATE TABLE #(ID int)

INSERT INTO #
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4 UNION ALL 
SELECT 5

SELECT 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
*/

------解决方案--------------------
探讨

SQL code

CREATE TABLE #(ID int)

INSERT INTO #
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

SELECT LTRIM(A.ID)+LTRIM(B.ID)+LTRIM(C.ID)
FROM # A,# B, # ……