如何在2个表的MYSQL中使用MAX和COUNT函数

I am a newbie in MYSQL and had a question regarding the use of MAX and COUNT functions together in MYSQL. I have 2 tables worker and assignment and the primary key of worker is a foreign key in assignment table.

我是MYSQL的新手,对MYSQL中使用MAX和COUNT函数有疑问。我有2个表工作者和赋值,而worker的主键是赋值表中的外键。

I need to show the employees name and id and the total assignment assigned to him, and only show the person with the most assignment that is the employee with the most assignment.

我需要显示员工姓名和身份证以及分配给他的总分配,并且只显示分配最多的人员,即分配最多的员工。

my code is

我的代码是

SELECT worker.Wrk_ID, worker.Wrk_LastName, MAX(a.count_id)
FROM worker,
    (SELECT COUNT(assignment.Wrk_ID) as count_ID 
        FROM worker, assignment 
        WHERE worker.Wrk_ID = assignment.Wrk_ID 
        GROUP BY worker.Wrk_ID)as a
GROUP BY worker.Wrk_ID;

The code is giving an error no. #1054.

代码给出错误号。 #1054。

Please can anyone help me.

请任何人都可以帮助我。

Thanking you in anticipation.

感谢你在期待。

1 个解决方案

#1

Try something like this:

尝试这样的事情:

SELECT worker.Wrk_ID, worker.Wrk_LastName, S.Count
FROM worker 
JOIN
(SELECT Wrk_ID, COUNT(*) AS Count FROM Assignments 
GROUP BY Wrk_Id ORDER BY COUNT(*) DESC LIMIT 1) S
ON worker.Wrk_ID = S.Wrk_ID

If you want a list of employees sorted by their total assignments:

如果您需要按其总分配排序的员工列表:

SELECT w.WrkID, w.Wrk_LastName, COUNT(*) AS Assignments
FROM work w left join Assignments a
ON w.WrkID=a.WrkID
GROUP BY w.WrkID 
ORDER BY COUNT(*) DESC;

To allow multiple winners:

允许多个获胜者:

SELECT s.*, w.Wrk_Lastname FROM 
(
  SELECT  wrk_id , COUNT(*) AS tot_assignments 
  FROM Assignments 
  GROUP BY wrk_id 
  HAVING COUNT(*) = 
  (
   SELECT MAX(tot) FROM 
   (
     SELECT COUNT(*) AS TOT FROM Assignments GROUP BY wrk_id
    ) counts
  ) 
) winners  
INNER JOIN worker w ON s.wrk_id = w.wrk_id;

It can be slow since it does multiple GROUP BY. Doing it in separated steps in a procedure can be better.

它可能很慢,因为它有多个GROUP BY。在程序中的分开步骤中执行此操作可能会更好。

#1