生成随机瑞士国家识别号(AHV/AVS)

Question

我想要产生随机瑞士国家识别号码(AHV/AVS)。

我找到了一个能做到的网站，如果我查看同一个页面的源代码，我也可以看到JavaScript 可以生成它的代码。

这些数字是按照以下模式生成的：

​

​

1. 756：是前缀号码，它从不更改
2. 1234：是一个随机数
3. 5678：是一个随机数
4. 9：是一个随机数
5. 7：是由此计算生成的控制号：
   • 从第一个数开始，取每一个其他数并将它们加起来：7 + 6 + 2 + 4 + 6 + 8 = 33
   • 从drcond数开始，取每一个其他数字并与它们之和：5 + 1 + 3 + 5 + 7 + 9 = 30
   • 然后乘以第二个数x 3和第一个数之和：33 + (30 x 3) = 123
   • 现在让10减去该数字的模：10-(123%10) = 10-3 = 7

===>，这就是我们如何最终得到7，也就是最后一个数字<===。

我已经创建了一个SQL命令，它可以生成所需的随机数：

SELECT CONCAT('756.', 
FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000) , '.',
FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000) , '.',
ABS(CHECKSUM(NEWID()))%10
-- How to select one number out of two? 
)

这段代码生成了我需要的所有随机数，但是我忽略了如何从两个数字中选择一个数字。

我不知道T-SQL是否能够解析数字并从两个中选择一个。

Answer 1

此实现使用整数计算而不是子字符串来获取数字。

注意：，我在检查数字计算的最终结果中添加了%10，以转换10 -> 0。

-- Format Number
SELECT *, CAST( Prefix AS CHAR( 3 )) + '.' + CAST( Rnd1 AS CHAR( 4 )) + '.' + CAST( Rnd2 AS CHAR( 4 )) + '.' + CAST( Rnd3 AS CHAR( 1 )) + CAST( CheckDigit AS CHAR( 1 )) AS FinalNum
FROM(
    -- Step 2: calculate check digit. NOTE: I apply %10 to the result to convert 10 to 0
    SELECT *, ( 10 - (( OddDigits + ( EvenDigits ) * 3 ) % 10 )) % 10 AS CheckDigit
    FROM 
        -- Step 2: calculate Odd / Even digit sum. Note that prefix is hardcoded to 756
        ( SELECT Prefix, Rnd1, Rnd2, Rnd3,
            7 + 6 + (( Rnd1 % 1000 ) / 100 ) + ( Rnd1 % 10 ) + (( Rnd2 % 1000 ) / 100 ) + ( Rnd2 % 10 ) AS OddDigits,
            5 + ( Rnd1 / 1000 ) + (( Rnd1 % 100 ) / 10 ) + ( Rnd2 / 1000 ) + (( Rnd2 % 100 ) / 10 ) + Rnd3 AS EvenDigits
        FROM
            -- Step 1: initial random values. Note that prefix is hardcoded to 756
            ( VALUES( 756,
                CAST( FLOOR( RAND( CHECKSUM( NEWID())) * ( 9999 - 1000 ) + 1000 ) AS INT ),
                CAST( FLOOR( RAND( CHECKSUM( NEWID())) * ( 9999 - 1000 ) + 1000 ) AS INT ),
                ABS( CHECKSUM( NEWID())) % 10 ),
                -- Your original example. Should be removed in production code
                (756, 1234, 5678, 9))   AS RndNum( Prefix, Rnd1, Rnd2, Rnd3 )
        ) AS CtrlDigitCalculation
    ) AS FormattedNumber

结果

Prefix      Rnd1        Rnd2        Rnd3        OddDigits   EvenDigits  CheckDigit  FinalNum
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------------
756         3826        4185        8           34          34          9           756.8002.7335.52
756         1234        5678        9           33          30          7           756.1234.5678.97

更新

检查随机数生成器表达式的返回值，发现它可以返回一个5位值，即10000。

SELECT CAST( FLOOR( CAST( 1.0 AS FLOAT ) * ( 9999 - 1000 + 1 ) + 1000 ) AS INT )
-- Result: 10000

要修复它，需要删除"+ 1"：

SELECT CAST( FLOOR( CAST( 1.0 AS FLOAT ) * ( 9999 - 1000 ) + 1000 ) AS INT )
-- Result: 9999

Answer 2

首先，您当前的逻辑有缺陷，您可以得到一个0到9999之间的随机数，但是不能使这个值固定宽度。因此，如果您的随机数是7、9和2，那么您将得到792而不是00700090002。你需要添加前导零。我用CONCAT和RIGHT做这件事。

接下来，我将这个表达式移到FROM中，这样它就物化了，并且易于使用。然后，我们可以使用SUBSTRING和CONVERT获取数字的两个部分，并将其作为SUM，然后最后应用最终的逻辑。然后将句点注入字符串中。

SELECT V.NIN,
       STUFF(STUFF(STUFF(CONCAT(V.NIN,10 - ((Odds + (Evens * 3)) % 10)),12,0,'.'),8,0,'.'),4,0,'.')
FROM (VALUES(CONCAT(RIGHT(CONCAT('000','756'),3), 
                    RIGHT(CONCAT('0000',FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000)),4),
                    RIGHT(CONCAT('0000',FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000)),4),
                    ABS(CHECKSUM(NEWID()))%10)),
            ('756123456789'))V(NIN)
     CROSS APPLY (VALUES(CONVERT(int,SUBSTRING(V.NIN,1,1)) + CONVERT(int,SUBSTRING(V.NIN,3,1)) + CONVERT(int,SUBSTRING(V.NIN,5,1)) + CONVERT(int,SUBSTRING(V.NIN,7,1)) + CONVERT(int,SUBSTRING(V.NIN,9,1)) + CONVERT(int,SUBSTRING(V.NIN,11,1)),
                         CONVERT(int,SUBSTRING(V.NIN,2,1)) + CONVERT(int,SUBSTRING(V.NIN,4,1)) + CONVERT(int,SUBSTRING(V.NIN,6,1)) + CONVERT(int,SUBSTRING(V.NIN,8,1)) + CONVERT(int,SUBSTRING(V.NIN,10,1)) + CONVERT(int,SUBSTRING(V.NIN,12,1))))I(Odds,Evens)

但是，老实说，我认为这可能是应用程序的问题，而不是SQL。