Recently in order to achieve an automatic single function, is to order an average assigned to existing employees (fixed)
For example: [A, B, C, D]
If he will be assigned to A order in the end [B, C, D, A]
If E at this time there are new staff join in maintaining the current under the condition of insertion finally [B, C, D, E, A]

The order table
Id status staff_id
The status field (1 has sent 2 complete order)

The staff table
Id name is_del

 
The current SQL is: 
SELECT 
Staff. Id 
The FROM 
` ims_carwash_staff ` AS staff 
LEFT the JOIN ` ims_carwash_order ` AS ` order ` 
ON the staff. Id=order. Staff_id 
AND the order. The order_status=1 
WHERE the staff. Is_del=0 
GROUP BY the staff. Id 
ASC ORDER BY COUNT (ORDER id); 

But there is A problem: if A staff at B staff has not been assigned to orders (status=1) when completed the task (status=2), the order will be assigned to A staff

CodePudding user response:

You mean there is no single priority, and then if you have already completed a single second, again according to the row of employees work number/name?
If this is just in the sorting process: suppose order_status 0 no single, 1 have single, 2 complete, query the first completed singular sort again (can is full, also can be recently for a period of time)
Select the top 1 * from XXX
The order by order_status, complete the singular, staff work number or name

CodePudding user response:

The

Hello World, 1/f, reference response:

you mean there is no single priority, and then if you have already completed a single second, again according to the row of employees work number/name?
If this is just in the sorting process: suppose order_status 0 no single, 1 have single, 2 complete, query the first completed singular sort again (can is full, also can be recently for a period of time)
Select the top 1 * from XXX
The order by order_status, complete the singular, staff work number or name

The way you said I considered but if A B C completed orders have to 10 D at this time of A single new employees up to the system will first 10 single D staff

CodePudding user response:

refer to the second floor apples3388_ response:

Quote: Hello World, 1/f, reference response:

You mean there is no single priority, and then if you have already completed a single second, again according to the row of employees work number/name?
If this is just in the sorting process: suppose order_status 0 no single, 1 have single, 2 complete, query the first completed singular sort again (can is full, also can be recently for a period of time)
Select the top 1 * from XXX
The order by order_status, complete the singular, staff work number or name

The way you said I considered but if A B C completed orders have to 10 D at this time of A single new employees up to the system will first D staff 10 single

Is see you have to list first, and then only consider single history, and history list can limit time, such as a month, according to the periodic adjustment, new employees came in also can consider the weight, add one more parameter

CodePudding user response:

You the real-time requirements of some subtle, don't put A minute ago, after A task assigned to A one minute after the task to E?

 
SELECT * 
The FROM 
(SELECT ID, 1 AS SORT_ID 
FROM the STAFF A 
WHERE NOT the EXISTS (SELECT 1 FROM the ORDER WHERE A.S TAFF_ID=STAFF_ID AND STATUS=1) 
AND IS_DEL=0 
UNION ALL 
SELECT ID, 2 AS SORT_ID 
FROM the STAFF A 
WHERE the EXISTS (SELECT 1 FROM the ORDER WHERE A.S TAFF_ID=STAFF_ID AND STATUS=1) 
AND IS_DEL=0) AS A 
The ORDER BY SORT_ID, ID