sql - 已分配最大员工人数的项目

Question

我有这些包含以下列的表格：

Employee24 (EMPLOYEEID, FIRSTNAME, LASTNAME, GENDER);

PROJECT24   (PROJECTID  PROJECTNAME EMPLOYEEID);

我想编写一个查询来查找分配了最大员工数的项目。

SELECT FIRSTNAME, LASTNAME
 FROM EMPLOYEE24 E
  WHERE E.EMPLOYEEID IN ( SELECT L2.EMPLOYEEID 
                            FROM PROJECT24 L2 group by l2.employeeid)\\ 

标签： sqloracleoracle11g

Answer 1

您可以尝试这样的事情（尽管我很确定它可以通过其他方式完成）：

SELECT *
FROM   (SELECT   prj.projectid,
                 prj.projectname,
                 COUNT(*) AS number_employees
        FROM     project24  prj
        JOIN     employee24 emp
          ON     prj.employeeid = emp.employeeid
        GROUP BY prj.projectid,
                 prj.projectname
        ORDER BY number_employees DESC)
WHERE  ROWNUM = 1;