TSQL foreach 查询 [英] TSQL foreach query

问题描述

我正在尝试为我数据库中的所有人生成一个随机 SSN，但是我在这样做时遇到了一些困难.

I am trying to generate a random SSN for all the people in my database, however I'm having a little difficulty doing so.

这是我正在使用的查询:

This is the query I'm using:

UPDATE tblTenant SET SSNO = (
    SELECT 
        CAST(A.A as CHAR(1)) + CAST(A.B as CHAR(1)) + CAST(A.C as CHAR(1)) + '-' +
        CAST(A.D as CHAR(1)) + CAST(A.E as CHAR(1)) + '-' +
        CAST(A.F as CHAR(1)) + CAST(A.G as CHAR(1)) + CAST(A.H as CHAR(1))+ CAST(A.I as CHAR(1))
    FROM
        (SELECT CAST(RAND() * 10 AS TINYINT) as A, CAST(RAND() * 10 AS TINYINT) as B, CAST(RAND() * 10 AS TINYINT) as C, CAST(RAND() * 10 AS TINYINT) as D, CAST(RAND() * 10 AS TINYINT) as E, CAST(RAND() * 10 AS TINYINT) as F, CAST(RAND() * 10 AS TINYINT) as G, CAST(RAND() * 10 AS TINYINT) as H, CAST(RAND() * 10 AS TINYINT) as I) as A
    );

它可以正确生成随机 SSN，但它只执行一次，即每个人都获得相同的 SSN.

It works correctly for generating a random SSN, however it only executes once, i.e. everyone gets the same SSN.

我怎样才能让每个用户生成一个新的随机 SSN?

How can I make it so that every user generates a new random SSN?

推荐答案

看看 在此 和之前的 SO 答案:如何为 TSQL Select 中的每一行生成随机数?

Have a look at this and this previous SO answer: How do I generate random number for each row in a TSQL Select?

这篇关于TSQL foreach 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！