从两个或多个部门查询员工 [英] Query an employee from two or more departments

问题描述

执行查询时发生错误。我不知道什么似乎是这个问题。我试图查询数据库，以找出两个或更多部门的工作人员。 （即Staff_ID = 1，在DEPT_ID = 4和6下工作）。有三个表：

I'm having an error when executing the query. I don't know what seems to be the problem. I'm trying to query the DB to to find out the staffs from two or more departments. (ie. Staff_ID = 1, works under the DEPT_ID = 4, and 6). There are three tables involved:

1. STAFF
2. DEPARTMENT
3. STAFF_DEPT (contains ID of STAFF and DEPT)

这是我做的，

SELECT
sd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name",
d.DEPT_NAME AS "Department"
FROM STAFF_DEPT sd
INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID 
INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID
GROUP BY sd.STAFF_ID
HAVING COUNT (sd.STAFF_ID) > 1

推荐答案

以下是您的原始查询：

SELECT sd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name",
       d.DEPT_NAME AS "Department"
FROM STAFF_DEPT sd
INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID 
INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID
GROUP BY sd.STAFF_ID
HAVING COUNT (sd.STAFF_ID) > 1;

您的查询的问题是您在 staff_id ，但您的聚合中有列 staff_name 和 dept_name 。您正在寻找多个部门的工作人员。您可以使用以下方式为每个人获取一行部门列表：

The problem with your query is that you are doing an aggregation on staff_id, but you have the columns staff_name and dept_name in your aggregation. You are looking for staff in multiple departments. You can get one row per person with a list of departments using:

SELECT sd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name",
       list_agg(d.DEPT_NAME, ',') within group (order by DEPT_NAME) AS "Department_List"
FROM STAFF_DEPT sd
INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID 
INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID
GROUP BY sd.STAFF_ID, s.STAFF_Name
HAVING COUNT (sd.STAFF_ID) > 1;

注意：我添加了 list_agg（）在组中选择和 s.staff_name >。

Notice: I've adding list_agg() in the select and s.staff_name in the group by.

如果每行需要一个人/部门，则使用具有分析函数的子查询：

If you want one person/department per row, then use a subquery with an analytic function:

selectsd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name", dept_name
from (select sd.staff_id, s.staff_name, d.dept_name,
             count(*) over (partition by sd.staff_id, s.staff_name) as NumDepts
      FROM STAFF_DEPT sd
      INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID 
      INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID
     ) t
where NumDepts > 1;

这篇关于从两个或多个部门查询员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！