选择随机名称 [英] Picking Random Names

问题描述

我前段时间看到了一个有趣的帖子，但没有解决方案.在这里试试运气:

I saw an interesting post sometime back but with no solution. Trying luck here:

有一个包含 10 个名称的表格(U1、U2、U3...等等).我每天必须选择 5 个名字，并显示一个作为编辑器和 4 个作为贡献者

There is a table which contain 10 names (U1, U2, U3..and so on). I have to choose 5 names everyday, and display one as the Editor and 4 as Contributors

在选择随机名称时，我还必须考虑到，如果一个用户被选为编辑器，则在每个人都有机会之前，他不能再次成为编辑器.

While selecting the random names, I have to also consider that if one user is selected as Editor, he cannot become editor again till everyone got their chance.

输出应类似于以下内容:

The output should look similar to the following:

           Editor   Cont1   Cont2     Cont3    Cont4
20-Jun   U1      U8       U9         U3       U4
21-Jun    U7      U2       U5         U6       U10
22-Jun    U3      U4       U9         U2       U8
23-Jun      U4      U8       U3          U5      U2
and so on..

推荐答案

这可能是一种方法.最有可能的是，较短的版本是可能的，但输出似乎符合您的要求.

This migth be one way to do it. Most likely, shorter versions are possible but the output seem to match your requirements.

解决方案的要点如下

• 为每个用户添加一个计数器，显示用户担任编辑的次数和贡献者的次数.
• 使用 TOP 1 和 NEWID() 从所有具有最低 EditorCount 的用户中选择一个随机用户并更新该用户的 EditorCount.
• 贡献者的选择也是如此.从所有具有最低 ContributorCount 的用户中选择一个随机用户，不包括刚刚成为编辑/贡献者的用户并更新该用户的 ContributeCount.

• Add a counter for every user for how many times a user has been an editor and how many times he has been a contributor.
• Select one random user from all users with the lowest EditorCount using a TOP 1 and NEWID() and update that user's EditorCount.
• Likewise the selection(s) for contributors. Select one random user from all users with the lowest ContributorCount, excluding users who just been made editor/contributor and update that user's ContributeCount.

SQL 脚本

SET NOCOUNT ON

DECLARE @Users TABLE (
  UserName VARCHAR(3)
  , EditorCount INTEGER
  , ContributorCount INTEGER
)

DECLARE @Solutions TABLE (
  ID INTEGER IDENTITY(1, 1)
  , Editor VARCHAR(3)
  , Contributor1 VARCHAR(3)
  , Contributor2 VARCHAR(3)
  , Contributor3 VARCHAR(3)
  , Contributor4 VARCHAR(3)
)

DECLARE @Editor VARCHAR(3)
DECLARE @Contributor1 VARCHAR(3)
DECLARE @Contributor2 VARCHAR(3)
DECLARE @Contributor3 VARCHAR(3)
DECLARE @Contributor4 VARCHAR(3)

INSERT INTO @Users
SELECT 'U1', 0, 0
UNION ALL SELECT 'U2', 0, 0
UNION ALL SELECT 'U3', 0, 0
UNION ALL SELECT 'U4', 0, 0
UNION ALL SELECT 'U5', 0, 0
UNION ALL SELECT 'U6', 0, 0
UNION ALL SELECT 'U7', 0, 0
UNION ALL SELECT 'U8', 0, 0
UNION ALL SELECT 'U9', 0, 0
UNION ALL SELECT 'U0', 0, 0

/* Keep Generating combinations until at least one user has been editor for 10 times */
WHILE NOT EXISTS (SELECT * FROM @Solutions WHERE ID = 30)
BEGIN
  SELECT  TOP 1 @Editor = u.UserName
  FROM    @Users u
          INNER JOIN (
            SELECT  EditorCount = MIN(EditorCount)
            FROM    @Users
          ) ec ON ec.EditorCount = u.EditorCount
  ORDER BY NEWID()
  UPDATE @Users SET EditorCount = EditorCount + 1 WHERE UserName = @Editor
  INSERT INTO @Solutions VALUES (@Editor, NULL, NULL, NULL, NULL)  

  SELECT  TOP 1 @Contributor1 = u.UserName
  FROM    @Users u
          INNER JOIN (
            SELECT  ContributorCount = MIN(ContributorCount)
            FROM    @Users
          ) ec ON ec.ContributorCount = u.ContributorCount
  WHERE   UserName <> @Editor
  ORDER BY NEWID()
  UPDATE @Users SET ContributorCount = ContributorCount + 1 WHERE UserName = @Contributor1
  UPDATE @Solutions SET Contributor1 = @Contributor1 WHERE Contributor1 IS NULL

  SELECT  TOP 1 @Contributor2 = u.UserName
  FROM    @Users u
          INNER JOIN (
            SELECT  ContributorCount = MIN(ContributorCount)
            FROM    @Users
          ) ec ON ec.ContributorCount = u.ContributorCount
  WHERE   UserName NOT IN (@Editor, @Contributor1)
  ORDER BY NEWID()
  UPDATE @Users SET ContributorCount = ContributorCount + 1 WHERE UserName = @Contributor2
  UPDATE @Solutions SET Contributor2 = @Contributor2 WHERE Contributor2 IS NULL

  SELECT  TOP 1 @Contributor3 = u.UserName
  FROM    @Users u
          INNER JOIN (
            SELECT  ContributorCount = MIN(ContributorCount)
            FROM    @Users
          ) ec ON ec.ContributorCount = u.ContributorCount
  WHERE   UserName NOT IN (@Editor, @Contributor1, @Contributor2)
  ORDER BY NEWID()
  UPDATE @Users SET ContributorCount = ContributorCount + 1 WHERE UserName = @Contributor3
  UPDATE @Solutions SET Contributor3 = @Contributor3 WHERE Contributor3 IS NULL

  SELECT  TOP 1 @Contributor4 = u.UserName
  FROM    @Users u
          INNER JOIN (
            SELECT  ContributorCount = MIN(ContributorCount)
            FROM    @Users
          ) ec ON ec.ContributorCount = u.ContributorCount
  WHERE   UserName NOT IN (@Editor, @Contributor1, @Contributor2, @Contributor3)
  ORDER BY NEWID()
  UPDATE @Users SET ContributorCount = ContributorCount + 1 WHERE UserName = @Contributor4
  UPDATE @Solutions SET Contributor4 = @Contributor4 WHERE Contributor4 IS NULL

END

SELECT * FROM @Solutions
SELECT * FROM @Users

这篇关于选择随机名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！