Get a random value from a range in MS SQL?

问题:

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 can be inserted into table)

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.

回答1:

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

EDIT

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.

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

回答2:

DECLARE @t TABLE(id INT)
INSERT INTO @t (id) 
VALUES 
(1),(19),(3)

;WITH numbers AS (
SELECT ROW_NUMBER() OVER(ORDER BY o.object_id,o2.object_id) RN FROM sys.objects o
CROSS JOIN sys.objects o2
),  NotExisted AS(
SELECT * FROM numbers WHERE RN NOT IN (SELECT ID FROM @t)
AND RN<1000)
SELECT TOP 1 RN FROM NotExisted ORDER BY NEWID()

回答3:

You will have to write a T-SQL to first query and find the gaps. There is no ready made SQL that will give you the gaps directly.