子查询和JOIN在COUNT上应用MAX SQL Oracle

rrup 发表于 Dev

鲁普

我正在尝试列出分配了最多工作人员的项目。

该列表仅列出给定项目中最多的员工人数。

SELECT MAX(number_employees)
FROM
    (SELECT COUNT(a.employee_id) AS number_employees
    FROM assignment a
    JOIN project p
    ON p.project_id = a.project_id
    GROUP BY a.project_id, p.project_name);

这列出了所有项目以及员工人数...

    
SELECT a.project_id, p.project_name, COUNT(a.employee_id) AS number_employees
    FROM assignment a
    JOIN project p
    ON p.project_id = a.project_id
    GROUP BY a.project_id, p.project_name;

但是，我试图列出所有项目，但仅列出员工人数最多的项目。

谢谢

大力水手

您可以FETCH按以下方式使用第一个查询with子句：

SELECT a.project_id, p.project_name, COUNT(a.employee_id) AS number_employees
 FROM assignment a
 JOIN project p ON p.project_id = a.project_id
GROUP BY a.project_id, p.project_name
order by number_employees desc
fetch first 1 row WITH TIES

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。