我看到了一个有趣的某个时候后回来，但没有解决方案。 这里尝试运气：
有包含10名（U1，U2，U3..and等）的表。 我必须每天选择5名，并显示一个作为编辑器和4贡献者
同时选择随机的名字，我也必须考虑到，如果一个用户选择作为编辑，他不能再成为编辑，直到每个人都得到了他们的机会。
输出应类似于以下内容：
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..
这migth是做到这一点的方法之一。 最有可能的，更短的版本是可能的，但输出似乎满足您的要求。
该解决方案的要点去如下
TOP 1和NEWID()和更新用户的EditorCount。 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
下面是一些伪C＃代码。
假设你有两个表
1） 用户表，该表包含所有用户
2），其中包含日常选择的用户DailyTeam表（您的输出）
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
注：我会实现这个算法在PowerShell中。
下面就让我来解释我的解决方案或者我应该说的逻辑，因为我在一个地方，我没有访问SQL Server。 所以我不能测试它， 你可能需要编辑，使其工作 。 所以，解释我的逻辑是什么..
首先假设你将追加一列（这是必须为这个逻辑）在现有的表说：“unirow”，这将有一个分配给每个员工从1开始的唯一编号的。
然后，同比增长必须创建一个表tbl_counter有一列作为number.There将只有一个行（限制），最初让它成为1。
由于prerequisit完成后，现在让我们继续前进逻辑。 我所做的一切是由自交叉联接Employees表五倍，让你有团队的独特组合。 现在，所有需要做的就是挑选独特的编辑每次执行该查询/操作时间。 此查询的输出/程序将包含5列第一位 - 编辑和休息贡献者。
开始
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
结束
