需要为工作人员分配任务，最少的人应该是自动候选人 [英] Need to assign tasks to staff, the one with the least should be the automatic candidate

问题描述

亲爱的专家，



需要为员工分配任务。



1.下一个任务应该分配给最少的员工。



如何在MS SQL SERVER 2008中完成此任务？





主表

-------------



idno名字

--------------------------

0001 JAMES

0002 PAUL

0003 MAY

0004 PAT





任务表

-------------



IDNO任务 - 投诉

------------------------------

0001 John Smith

0001 Sarah Adams

0001 Michael Jones



0002 Godfred Arthur

0002 David Lawson



0003 Jennifer Bruce









下一个任务应该是去 0004 -PAT





请提供选择st将从任务表中挑选他的atement







谢谢



我尝试了什么：



我已经审查了一些代码但无济于事

Dear Expert,

Need to assign tasks to staff.

1. The next assignment should be assigned to the staff with the least.

How do you accomplish this in MS SQL SERVER 2008 ?


Master Table
-------------

idno name
--------------------------
0001 JAMES
0002 PAUL
0003 MAY
0004 PAT


TASKS TABLE
-------------

IDNO TASKS-COMPLAINT
------------------------------
0001 John Smith
0001 Sarah Adams
0001 Michael Jones

0002 Godfred Arthur
0002 David Lawson

0003 Jennifer Bruce




The next assignment should be go to 0004 -PAT


Please provide the select statement which will pick him from the task table



Thanks

What I have tried:

I have reviewed a number of codes but to no avail

推荐答案

根据我的评论，第一次运行此任务时，应将任务分配给004-PAT，因为它们目前根本没有任何任务分配给它们。



此代码将执行您想要的操作：

As per my comment, the first time you run this the task should be assigned to 004 - PAT as they currently do not have any tasks at all assigned to them.

This code will do what you want:

UPDATE [tasks] SET AssignedTo = 
	(select TOP 1 idno
	from [Master] M left outer join [tasks] T on M.idno=T.AssignedTo
	group by idno
	order by COUNT(t.taskID) )
WHERE AssignedTo IS NULL

注意事项：

- 使用 TOP 1 只获得一个idno

- The ORDER BY 所以分配最少的那个过滤到顶部

- 我使用了这样的表

Things to note:
- the use of TOP 1 to only get a single idno
- The ORDER BY so the one with the least assignments filters to the top
- I used a table like this

creeate table [tasks]
(
	taskID int identity(1,1),
	Task nvarchar(150),
	AssignedTo int
)

- 注意 WHERE 子句所以我只更新未分配的记录



第一次运行此代码时，您可能会收到有关NULL值的警告

- Note the WHERE clause so I only update the unassigned records

The first time you run this code you may get a warning about NULL values

Quote：

警告：Null值被消除聚合或其他SET操作。

Warning: Null value is eliminated by an aggregate or other SET operation.

这可以忽略，并且一旦[Master]表上的所有条目都分配了至少一个任务就不会出现



要明确我用过的以下测试数据

This can be ignored and will not appear once all entries on the [Master] table have at least one Task assigned to them

To be clear I used the following test data

insert into [Master] values
('JAMES'),   -- Id = 1 (Autogenerated)
('PAUL'),    -- Id = 2
('MAY'),     -- Id = 3
('PAT')      -- Id = 4

insert into [tasks] values
('John Smith',1),       -- assigned to JAMES
('Sarah Adams',1),      -- assigned to JAMES
('Michael Jones',1),    -- assigned to JAMES
('Godfred Arthur',2),   -- assigned to PAUL
('David Lawson',2),     -- assigned to PAUL
('Jennifer Bruce',3),   -- assigned to MAY
('new item',null)       -- assigned to NO-ONE!

所以起点是：

JAMES分配了3个任务

PAUL分配了2个任务<可以分配1个任务。
可以分配1个任务

PAT没有分配任务。

桌子上有7个任务，除了其中一个任务外分配给某人。



外推本节上方的代码

So the starting point is:
JAMES has 3 tasks assigned
PAUL has 2 tasks assigned
MAY has 1 task assigned
PAT has no tasks assigned to them.
There are 7 tasks on the table, all but one of them are assigned to someone.

Extrapolating the code above this section

--select TOP 1 idno
SELECT idno, name, count(t.taskID)
	from [Master] M left outer join [tasks] T on M.idno=T.AssignedTo
	group by idno, name
	order by COUNT(t.taskID)

给出结果

gives the results

4       PAT	0
3	MAY	1
2	PAUL	2
1	JAMES	3

所以我实际使用的位

select TOP 1 idno
	from [Master] M left outer join [tasks] T on M.idno=T.AssignedTo
	group by idno
	order by COUNT(t.taskID)

将返回值 4 。

该值将被分配给 AssignedTo 列为空的表上的所有行。



重要的是，在评论的情况下，如果AssignedTo列中没有行具有空值，那么将不会更新任何内容，并且主表上的行将保持不变



如果我添加新行

will return the value 4.
That value will be assigned to all rows on the table where the AssignedTo column is null.

Importantly, as in your case in the comments, if no rows have a null value in the AssignedTo column then nothing will be updated and the counts for each row on the Master table will remain unchanged

If I add a new row

insert into tasks values ('item 2',null)

并再次运行代码，第二次通过MAY和PAT每次只有1个任务。可能只是因为Id低于PAT而被分配了新任务，但这不能保证。

and run the code again, the second time through both MAY and PAT only have 1 task each. It's likely that MAY be assigned the new task simply because that Id is lower than PAT, but this is not guaranteed.

这篇关于需要为工作人员分配任务，最少的人应该是自动候选人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！