MySql教科书语法错误？

Question

我有 3 个表:Employee(EmpCode,EmpName,Birth,DeptCode)部门(部门代码、部门名称、位置、电话)项目(项目代码、项目名称、预算)参与(EmpCode、PrCode、小时)。

我想找到参与所有项目的员工。我的教科书使用此查询来查找在项目 1 和项目 2 中工作的员工的 EmpCode:select EmpCode from Participate group by EmpCodehaving 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 );

Answer 1

您收到语法错误的原因是您的 IN 子句。您正在查询两列:

SELECT EmpCode AS Employee_Code, DeptCode

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

WHERE EmpCode IN (..)

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

• 更改 WHERE 子句以检查 WHERE EmpCode, DeptCode IN (...)
• 将子查询更改为仅选择 EmpCode，但根据您的聚合，我认为第一个选项是您想要获得正确结果的方法。

编辑:

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