Picking Random Names

I saw an interesting p开发者_如何学Goost sometime back but with no solution. Trying luck here:

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.

The gist of the solution goes as follows

• 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 Script

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

Here is some pseudo C# code.

Assuming you have two tables

1) User table which contains all the users

2) DailyTeam table which contains the users selected daily (your output)

struct Team 
{
    string name;
    int editorCount;
}
currentEditorList is a List of Team 
existingUserList is a List of Team 

currentEditorList = Get Current Editor List from DailyTeam
existingUserList = Get All Users from User and its editor count (may need left outer join)


todayTeam is a new Array

// populate the normal users to dailyTeam
while (todayTeam count is less than 4)
{
    randomIndex = generate a random number (from 0 to 9)
    userName = get name from existingUserNames[randomIndex]
    if (userName is not in todayTeam)
    {
       add userName to todayTeam
    }
}


sort existingUserList by its editorCount
editorName = get the first item from existingUserList
add editorName to todayTeam

Note: I would implement this algorithm in powershell.

Here let me explain my solution or I should say logic, because I'm at a place where I DON'T have access to SQL Server. So I'm not able to test it, you may have to edit to make it work. So explaining what my logic is..

2. First of all assuming that you will append a column (WHICH IS MUST for this logic)in your existing table say "unirow" which will have a unique number assigned to each employee starting from 1.

3. Then yoy have to create a table tbl_counter with one column as number.There will be only one row (restriction) and initially let it be 1.

4. As prerequisit is complete, now let's move to logic. All I did is made a self cross join for the Employees table five times so that you have a unique combination of team. Now all need to done is to pick unique Editors each time this query/procedure is executed. The output of this query/procedure will contain 5 columns 1st for editor and rest for Contributors.

BEGIN

DECLARE @counter number
DECLARE @limit number
DECLARE @Editor varchar(100)


select @limit=count(*) from Employees

select @counter=counter+1 from tbl_counter

IF(@counter>@limit)
begin
     set @counter=1
     update tbl_counter set counter=1
end

select @Editor=Name from Employees2 where id=@counter

select  top 1 newid() as unirow,t1.name Editor,t2.name Contributor1,
t3.name Contributor2,t4.name Contributor3,t5.name Contributor4
from Employees t1,Employees t2,Employees t3,Employees t4,Employees t5
where t1.name<>t2.name and t1.name<>t3.name and t1.name<>t4.name and t1.name<>t5.name
and t2.name<>t1.name and t2.name<>t3.name and t2.name<>t4.name and t2.name<>t5.name
and t3.name<>t2.name and t3.name<>t1.name and t3.name<>t4.name and t3.name<>t5.name
and t4.name<>t2.name and t4.name<>t3.name and t4.name<>t1.name and t4.name<>t5.name
and t5.name<>t2.name and t5.name<>t3.name and t5.name<>t4.name and t5.name<>t1.name
and t1.name=@Editor
order by unirow 

END