mysql - MySql教科书语法错误？

我有3个表：Employee（EmpCode，EmpName，Birth，DeptCode）
部门（DeptCode，DeptName，位置，电话）
项目（PrCode，PrName，预算）
参加（EmpCode，PrCode，小时）。

我想找到参与所有项目的员工。我的课本使用此查询来查找在Project 1和Project2中工作的员工的EmpCode：select EmpCode from Participate group by EmpCode having set(PrCode) contains('Pr1','Pr2')

使用此查询，我找到了参与所有项目的员工：

select EmpName,Birth, DeptCode
from Employee
where EmpCode in (
    select EmpCode as Employee_Code,DeptCode
    from Participate
    group by DeptCode
    having set(Employee_Code) contains EmpCode );

但这给了我一个语法错误！请帮我非常感谢。
P / S：这是完整的代码：

create table Employee(
EmpCode int unsigned not null auto_increment,
EmpName varchar(40) not null default '',
Birth varchar(40) not null default '',
DeptCode int unsigned not null default 0,
primary key (EmpCode)
);

create table Dept(
DeptCode int unsigned not null auto_increment,
DeptName varchar(40) not null default '',
Location varchar(40) not null default '',
Tel varchar(40) not null default '',
primary key (DeptCode)
);

create table Project(
PrCode int unsigned not null auto_increment,
PrName varchar(40) not null default '',
Budget varchar(40) not null default '',
primary key (PrCode)
);

create table Participate(
EmpCode int unsigned not null ,
DeptCode int unsigned not null ,
Hours int unsigned not null default 0,
primary key (EmpCode, DeptCode)
);


insert into Employee values
(501,'Nguyen Van A', '26/03/1974',301),
(502,'Pham Thi B', '10/01/1990',302),
(503,'abcdsfdsf', '06/06/1991',302),
(570,'Tran Minh T', '25/02/1985',303);

insert into Dept values
(301,'HR','Bi mat','029393943'),
(302,'R&D','Quan net','01938228329'),
(303,'PR','Everywhere','024032940');

insert into Project values
(201,'on linear programming','1 trieu'),
(203,'military secret','1 ty ty'),
(204,'Skynet','1 ty USD'),
(210,'Terminator','100 trieu');

insert into Participate values
(501, 201, 1000),
(501, 203, 2000),
(502, 204, 10),
(503, 210, 220),
(501, 204, 7474),
(501, 210, 103),
(570, 204, 11);

alter table Participate
    add foreign key (EmpCode) references Employee(EmpCode),
    add foreign key (PrCode) references Project(PrCode);

alter table Employee
    add foreign key (DeptCode) references Dept(DeptCode);

select EmpName,Birth, DeptCode
    from Employee
    where EmpCode in (
        select EmpCode as Employee_Code,DeptCode
        from Participate
        group by DeptCode
        having set(Employee_Code) contains EmpCode );

最佳答案

出现语法错误的原因是由于您的IN子句。您要查询两列：

SELECT EmpCode AS Employee_Code, DeptCode

但是，您仅针对一列进行检查：

WHERE EmpCode IN (..)

要解决此问题，您需要根据您的问题执行以下两项操作之一：


更改您的WHERE子句以检查两个WHERE EmpCode, DeptCode IN (...)
将您的子查询更改为仅选择EmpCode，但是基于您的汇总，我认为第一个选项是您要执行的操作以获得正确的结果。


编辑：

另一个可能的语法错误可能是在Haven子句中使用表别名。您可能需要将其更改为HAVING SET(EmpCode)，而不是使用Employee_Code别名。