如何显示部门（一次）和在该部门工作的所有员工的列表？

Edgar 发表于 Dev

埃德加

对于epartments(department_id, department_name) employees(last_name, department_id, salary)
要显示的架构，department_id, department_name, count(employees),avg(salary),last_name,salary
我尝试使用以下查询

SELECT d1.department_id,d1.department_name,d1."count",d1."avg",e.last_name,e.salary
FROM   employees e
INNER JOIN (SELECT d.department_id,d.department_name,count(e.last_name) AS "count",round(avg(e.salary),2) AS "avg"
                FROM employees e,departments d
                WHERE e.department_id=d.department_id
                GROUP BY d.department_id,d.department_name) d1
    ON e.department_id=d1.department_id;

当它显示正确的输出时，它不是我想要的格式。上面的查询给出的输出为

90  Executive   3   19333.33    King    24000
90  Executive   3   19333.33    Kochhar 17000
90  Executive   3   19333.33    De Haan 17000
60  IT          3   6400        Hunold  9000
60  IT          3   6400        Ernst   6000
60  IT          3   6400        Lorentz 4200
50  Shipping    5   3500        Mourgos 5800

虽然应该像

90  Executive   3   19333.33    King    24000
                                Kochhar 17000
                                De Haan 17000
60  IT          3   6400        Hunold  9000
                                Ernst   6000
                                Lorentz 4200
50  Shipping    5   3500        Mourgos 5800

如果您确实想''在查询中显示类似的内容而不是这些数据，那么我认为您可以使用ROW_NUMBER()以下方式：

CASE 
    WHEN (ROW_NUMBER() OVER (PARTITION BY d1.department_id
                             ORDER BY d1.department_id)) = 1 THEN
        d1.department_id
    ELSE
        Null
END

为列：d1.department_id，d1.department_name，d1."count"，d1."avg"。

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

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