主页 / dba / 问题 / 119116
Accepted
H. Pauwelyn
Asked: 2015-10-26 06:31:44 +0800 CST

用另一个表 B 中的随机 ID 填充表 A

  • 772

我有两个表，我想用随机值的表的 ID更新表中的列。GebruikerIDTopicGebruiker

您还必须了解以下事项：

  • Topic.GebruikerID参考Gebruiker.ID.
  • 并非每个 ID 都在两个表中都使用。
  • 必须生成的随机数存在于表Gebruiker（列ID）中，必须更新到表Topic（列GebruikerID）中。
  • 我在速成版中使用 SQL Server。

这里有一些图像。

  • 表之间的关系

  • Gebruiker-table的 ID

sql-server random

2 个回答

  1. Best Answer

    ROW_NUMBER您可以根据随机排序分配两个s 并加入它们：

    select
   t.id,
   g.id as gebruikerid
from
 (
   select id,
      (row_number() over (order by newid())
    % (select count(*) from gebruiker))+1 as rn -- number from 1 to n 
   from topic
 ) as t 
join 
 ( select id,
     row_number() over (order by newid()) as rn -- sequential number from 1 to n
   from gebruiker
 ) as g
  on t.rn = g.rn 
order by t.id

    见小提琴

    编辑：

    您可以使用此 SELECT 的结果来更新Topic表：

    update t
set gebruikerid = dt.gebruikerid
from topic as t
join
 (
   select
      t.id,
      g.id as gebruikerid
   from
    (
      select id,
         (row_number() over (order by newid()) % (select count(*) from gebruiker))+1 as rn
      from topic
    ) as t 
   join 
    ( select id,
         row_number() over (order by newid()) as rn
      from gebruiker
    ) as g
   on t.rn = g.rn 
 ) as dt
on t.id = dt.id;

    小提琴

    • 5

  2. 死灵术。如果主题只有一个条目，并且 gebruiker 只有一个条目，则接受的答案将失败。

    您需要从 row_number 中减去 -1：

    SELECT
     t.id
    ,g.id AS gebruikerid
FROM
(
    SELECT 
         id
        ,
        (
            (ROW_NUMBER() OVER (ORDER BY NEWID() ) -1) 
            % 
            (SELECT COUNT(*) FROM gebruiker) 
        ) + 1 AS rn -- number FROM 1 to n 
    FROM topic
) AS t 

INNER JOIN 
( 
    SELECT 
         id 
        ,ROW_NUMBER() OVER (ORDER BY NEWID() ) AS rn -- sequential number FROM 1 to n 
    FROM gebruiker 
) AS g 
    ON t.rn = g.rn 

ORDER BY t.id

    然后，您可以使用 CTE 进行更新：

    ;WITH CTE AS 
(
    SELECT
         t.id 
        ,t.GebruikerID 
        ,g.id AS gebruikerid
    FROM
    (
        SELECT 
             id
            ,GebruikerID 
            ,
            (
                (ROW_NUMBER() OVER (ORDER BY NEWID() ) -1) 
                % 
                (SELECT COUNT(*) FROM gebruiker) 
            ) + 1 AS rn -- number FROM 1 to n 
        FROM topic
    ) AS t 

    INNER JOIN 
    ( 
        SELECT 
             id 
            ,ROW_NUMBER() OVER (ORDER BY NEWID() ) AS rn -- sequential number FROM 1 to n 
        FROM gebruiker 
    ) AS g 
        ON t.rn = g.rn 
)
-- SELECT * FROM CTE 
UPDATE CTE SET GebruikerID = gebruikerid
    • 1

相关问题