时间戳

首页 > 解决方案 > 如何正确编写此查询？

sql - 如何正确编写此查询？

问题描述

如何编写一个查询，提供国家名称、城市、邮政编码、街道地址和至少有 2 名员工工作的部门数量？以下是我编写的查询，但由于查询，我得到“不是 GROUP BY 表达式”错误。

SELECT k.COUNTRY_NAME,
       l.CITY,
       l.POSTAL_CODE,
       l.STREET_ADDRESS,
       e.DEPARTMENT_ID,
       COUNT(EMPLOYEE_ID)
FROM hr.employees e
    JOIN hr.departments c
        ON (c.DEPARTMENT_ID = e.DEPARTMENT_ID)
    JOIN hr.locations l
        ON (c.LOCATION_ID = l.LOCATION_ID)
    JOIN hr.countries k
        ON (k.COUNTRY_ID = l.COUNTRY_ID)
GROUP BY e.DEPARTMENT_ID
HAVING COUNT(EMPLOYEE_ID) > 2;

标签： sqloraclegroup-by

解决方案

您将首先获得至少有 2 名员工的所有部门的集合，如下所示（atleast_two）

之后，您会将数据与查询的其余部分连接起来，并提取感兴趣的属性。

with atleast_two
  as (select c.DEPARTMENT_ID
            ,count(employee_id) as cnt_employees
        from hr.employees e 
        join hr.departments c 
          on (c.DEPARTMENT_ID=e.DEPARTMENT_ID) 
        group by c.deptid
        having count(employee_id)>2
      )
    select k.COUNTRY_NAME
          , l.CITY
          , l.POSTAL_CODE
          , l.STREET_ADDRESS
          , e.DEPARTMENT_ID
          , c.cnt_employees
      from hr.employees e 
      join atleast_two c 
        on (c.DEPARTMENT_ID=e.DEPARTMENT_ID) 
      join hr.locations l 
        on (c.LOCATION_ID=l.LOCATION_ID) 
      join hr.countries k 
        on (k.COUNTRY_ID=l.COUNTRY_ID);

推荐阅读