oracle hr样本模式,Oracle HR模式中的SQL

您正在按部门分组并希望显示部门所在城市。你希望这能奏效,因为每个部门都在同一个城市。(SQL人称之为函数依赖关系。)

为了让这个工作。。。

数据库管理系统必须检测和支持聚合查询中的函数依赖关系

不幸的是,Oracle不支持聚合查询中的函数依赖。它迫使我们把每一个这样的专栏

GROUP BY

所以要么延长

条款:

SELECT d.department_name, l.city, COUNT(e.department_id)

FROM departments d

JOIN employees e ON e.department_id = d.department_id

JOIN locations l USING (location_id)

GROUP BY d.department_name, l.city

ORDER BY d.department_name;

MIN

或

MAX

就那个单一的价值。

SELECT d.department_name, MAX(l.city) AS city, COUNT(e.department_id)

FROM departments d

JOIN employees e ON e.department_id = d.department_id

JOIN locations l USING (location_id)

GROUP BY d.department_name

ORDER BY d.department_name;

不过,我更喜欢先聚合,然后再加入。你想用员工人数加入各部门,那么就这样做:

SELECT d.department_name, l.city, COALESCE(e.cnt, 0) AS employee_count

FROM departments d

JOIN locations l USING (location_id)

LEFT JOIN

(

SELECT department_id, COUNT(*) as cnt

FROM employees

GROUP BY department_id

) e ON e.department_id = d.department_id

ORDER BY d.department_name;