时间戳

首页 > 解决方案 > 制作 100 个随机数并将它们作为主键添加到表中，如果重复则捕获错误

sql-server - 制作 100 个随机数并将它们作为主键添加到表中，如果重复则捕获错误

问题描述

我有个问题。我正在尝试添加 100 个随机主键并在值重复时捕获错误，然后添加计数错误数量的计数器，最后打印出 ID 中有多少值以及有多少重复数字错误。

到目前为止我已经这样做了，但我对 T-SQL 真的很陌生，我不确定这是否接近。

最大的问题是我不知道如何从 WITH 中获取值编号并将给定的编号插入表中。

DECLARE @TABLE TABLE (ID INT NOT NULL PRIMARY KEY)
DECLARE @I INT = 1
DECLARE @ERROR INT = 0
DECLARE @NUMBER INT

BEGIN TRY
    WITH CTE_Numbers(number) AS
    (
        SELECT 1 AS number
        UNION ALL 
        SELECT number + 1
        FROM CTE_Numbers
        WHERE number < 100 
    )
    SELECT TOP 1 number
    FROM CTE_Numbers
    ORDER BY NEWID()
    OPTION (MAXRECURSION 0)

    BEGIN
        WHILE @I <= 100
        BEGIN   
            SET @NUMBER = (SELECT number FROM CTE_Numbers)
            INSERT INTO @TABLE VALUES(@NUMBER)

            SET @I = @I + 1
        END
    END
END TRY
BEGIN CATCH
    SET @ERROR = @ERROR + 1
END CATCH

SELECT COUNT(ID) AS numbers, @ERROR AS errors
FROM @TABLE

标签： sql-servertsql

解决方案

我不完全明白你在做什么，但这应该让你开始。@rows 是您想要的行数，@high 是最高 ID（介于 1 和 @high 之间）。

--==== Parameters
DECLARE 
  @rows INT = 10, -- Return this many rows
  @high INT = 20;  -- Number = 1 to @high

--==== Prep
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;

--==== Create and Populate #t with random numbers 
WITH 
e1(x) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
t(N)  AS (SELECT ABS(CHECKSUM(NEWID())%@high)+1 FROM e1 a, e1 b, e1 c, e1 d) -- up to 10K
SELECT TOP(@rows) t.N INTO #t FROM t;

--==== Counts
SELECT
  UniqueIds  = SUM(IIF(f.T=1, 1, 0)),
  Duplicates = SUM(IIF(f.T=1, 0, 1))
FROM
(
  SELECT   t.N, COUNT(*)
  FROM     #t AS t
  GROUP BY t.N
) AS f(N,T);

--==== Santiy Check
SELECT   t.N, ttl = COUNT(*)
FROM     #t AS t
GROUP BY t.N

返回：（随机）

UniqueIds   Duplicates
----------- -----------
2           4

N           ttl
----------- -----------
2           2
5           1
6           2
8           2
12          2
14          1

推荐阅读