我正在使用的MySql数据库中有此表:
create table employees (
num_pass int(5) not null,
name varchar(40),
primary key (num_pass)
)engine=innodb;
create table laboratories (
code int(10) not null,
name varchar(40),
primary key (codi),
)engine=innodb;
create table areas (
code int(5) not null,
codeLab int(10) not null,
level enum('H','M','L'),
primary key (code, codeLab),
foreign key (codeLab) references laboratories(code)
)engine=innodb;
create table qualifieds (
num_pass int(5) not null,
area_assigned int(5),
lab int(5),
primary key (num_pass),
foreign key (num_pass) references employees(num_pass),
foreign key (area_assigned, lab) references areas (code, codeLab)
)engine=innodb;
现在我想知道哪些地区有三名以上合格的员工。具体来说,我想获取区号以及实验室名称,实验室以及按地区排序。
我试图使用此命令来获取该区域的代码
select b.code
from employees e, areas b, qualifieds q
where e.num_pass=q.num_pass
and 3 < (select count(b1.code)
from areas b1, qualifieds q1, employers e1
where e1.num_pass=q1.num_pass
and q1.area_assigned=b1.code
and q1.lab=b1.codeLab);
但是我所得到的是所有与员工人数重复多次的区号的列表(我有区号分别为1,2,3,4和6的员工,我得到的是序列1,2,3 ,4重复6次)。关于如何获取所需信息的任何想法?
最佳答案
您可以使用GROUP BY和HAVING查找包含3条以上记录的组...
select areas_assigned, lab, count(num_pass)
from qualifieds
group by areas_assigned, lab
having count(num_pass)>3;
如果您随后需要对此进行扩展,则添加联接...
select q.areas_assigned, l.name, count(q.num_pass)
join laboratories l on l.lab = q.lab
from qualifieds q
group by q.areas_assigned, l.name
having count(q.num_pass)>3;
关于mysql - 获取哪些地区拥有三名以上合格员工,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44484489/