TSQL foreach 查询 [英] TSQL foreach query
本文介绍了TSQL foreach 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试为我数据库中的所有人生成一个随机 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屋!
查看全文