SQL 列出参与多个项目的员工姓名

List the the name of employee that worked on more than one projects

提问by user1824931

There are two tables that would be relevant in this. Workon and Employee. The contents of these two tables are EMPID NAME SALARY DID (department ID) for employee and PID EMPID HOURS from work on. The SQL I have written is

有两个表与此相关。工作和员工。这两个表的内容是雇员的 EMPID NAME SALARY DID（部门 ID）和工作的 PID EMPID HOURS。我写的SQL是

select e.name, w.pid
from employee e, workon w
where e.empid = w.empid
group by e.name, w.pid, w.empid
having count (e.name) > 1
order by w.pid

I have been trying to figure out why this code will not give me employees that work on more than one project. Please help me figure out what I am doing wrong.

我一直试图弄清楚为什么这段代码不会让我从事多个项目的员工。请帮我弄清楚我做错了什么。

采纳答案by spioter

your group by was returning one row per employee per project, by definition this could never have more than 1 row

您的 group by 为每个项目的每个员工返回一行，根据定义，这永远不会超过 1 行

below sql should work

下面的sql应该可以工作

WARNING: i am not sure how this will affect performance of server use at your own risk

警告：我不确定这将如何影响服务器使用的性能，风险自负

following will return 1 row per employee per workon but be limited to employees with more than 1 workon record (so if employee has 5 workon records, you will get 5 rows with same e.name and then 5 different w.pid values)

以下将为每个员工每个工作返回 1 行，但仅限于具有 1 个以上工作记录的员工（因此，如果员工有 5 个工作记录，您将获得 5 行具有相同的 e.name 和 5 个不同的 w.pid 值）

select e.name, w.pid
from employee e, workon w
where e.empid = w.empid
and e.empid in (
select w.empid
from workon w -- there was a typo here originally
group by 1
having count (*) > 1
)
order by e.name, w.pid

回答by Merr Leader

SELECT E.NAME, W.PID
from employee e inner join workon w on e.empid = w.empid
where e.empid in (select EMPID from workon GROUP BY EMPID HAVING COUNT(EMPID) > 1)

The subquery is counting all records with that empid associated to it with more than 1 project, and the main Query is checking to see if the empid from empid table is in the subquery's results.

子查询正在计算与该 empid 关联的所有记录与 1 个以上的项目，并且主查询正在检查 empid 表中的 empid 是否在子查询的结果中。

回答by Suraj Kumar

You can try this below query.

您可以在下面的查询中尝试此操作。

Here in a variable all distinct count department has been taken from department master table. After that only those employee has been selected where count match with distinct linked department count in relation table.

在一个变量中，所有不同的计数部门都来自部门主表。之后，只选择那些计数与关系表中不同的链接部门计数匹配的员工。

CREATE TABLE employees
(
    employee_id int NOT NULL CONSTRAINT pk_employees PRIMARY KEY,
    employee_name nvarchar(128) NOT NULL CONSTRAINT uk_employees_employee_name UNIQUE
);

CREATE TABLE departments
(
    department_id int NOT NULL PRIMARY KEY,
    department_name nvarchar(128) NOT NULL CONSTRAINT uk_departments_department_name UNIQUE
);

CREATE TABLE department_employees
(
    department_id int NOT NULL CONSTRAINT fk_department_employees_departments REFERENCES departments(department_id),
    employee_id int NOT NULL CONSTRAINT fk_departement_employees_employees REFERENCES employees(employee_id),
    CONSTRAINT pk_deparment_employees PRIMARY KEY (department_id, employee_id)
)

INSERT INTO employees
VALUES (1, 'John Doe'), (2, 'Jane Doe'), (3, 'William Doe'), (4, 'Margaret Doe')

INSERT INTO departments
VALUES (1, 'Accounting'), (2, 'Humman Resources'), (3, 'Marketing')

INSERT INTO department_employees
VALUES 
    (1, 1), (2, 1), (3, 1), 
    (2, 2), (2, 3),
    (3, 3), (3, 4)

declare @distinctDeptCount int 
SET @DistinctDeptCount = (SELECT Count(Distinct department_id) FROM departments)
--SELECT @DistinctDeptCount

SELECT Distinct employees.employee_id, employee_name
from employees     
where employees.employee_id in (
select employee_id from department_employees GROUP BY employee_id HAVING COUNT(department_id) >= @distinctDeptCount
)

Here is the live demo Emp. with all Department The output is as shown below

这是现场演示Emp。与所有部门 的输出如下图

employee_id employee_name
1           John Doe