从 MS SQL 的范围中获取随机值? [英] Get a random value from a range in MS SQL?

问题描述

假设我的表可以有从 000 到 999 的值(三位数字且小于 1000)

Let suppose my table can have values from 000 to 999 (three digits and less than 1000)

其中一些值已填充.让我们假设目前我的桌子有

Some of this values are filled. Let's suppose currently my table has

000,002,005,190(001,004,003,006,..189,191,..,999 可以插入表中)

这些值是随机分配的，000和002在表中，但001还没有在表中.

and these values are randomly allocated 000 and 002 is in table but 001 is not in table yet.

我怎样才能获得可以插入到表中的值.

How can I get the values that I can insert into table yet.

推荐答案

DECLARE @t TABLE
(VALUE CHAR(3))

INSERT @t
VALUES
('000'),('002'),('005'),('190')


;WITH rnCTE
AS
(
    SELECT -1 + ROW_NUMBER() OVER (ORDER BY TYPE, number, name) AS rn
    FROM master.dbo.spt_values
)
SELECT RIGHT('000' + CAST( rn AS VARCHAR(11)),3)
FROM rnCTE
WHERE NOT EXISTS    (   SELECT 1 FROM @t 
                        WHERE VALUE = rn
                    )
AND rn < 1000

编辑

此查询通过从系统表 (master.dbo.spt_values) 生成可能数字的完整列表来工作，该表保证在 CTE rnCTE 内包含 1000 多行代码>.-1 被添加到 ROW_NUMBER 以使值从 0 而不是 1 开始.

This query works by generating the complete list of possible numbers from a system table (master.dbo.spt_values) which is guaranteed to contain more than 1000 rows inside the CTE rnCTE. -1 is added to ROW_NUMBER to have the values start at 0 rather than 1.

外部查询零填充数字以供显示，仅返回那些不在源数据中且小于 1000 的数字.

The outer query zero pads the numbers for display, returning only those which are not in the source data and are less than 1000.

这篇关于从 MS SQL 的范围中获取随机值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！