我有三个表：
组
id_group (PK) | name
registry_in_group
id_group (PK-FK) | id_registry (PK-FK)
注册表
id_registry (PK) | name | surname | dob (DATE)
我必须选择仅包含年龄大于18岁的注册表的组名。
这是我的查询
SELECT g.name
FROM group as g
JOIN registry_in_group as aig
ON g.id_group = aig.id_group
JOIN registry as a
ON aig.id_registry = a.id_registry
AND TIMESTAMPDIFF(YEAR,a.dob,CURDATE()) >= 18
但是此收益也降低了注册表18。为什么？
查询的问题在于，它查找具有至少一个早于18个注册表的组，而您要确保该组中的所有 个注册表都满足该条件。
您可以使用条件为not exists的相关子查询来筛选没有注册表小于18岁的组：
select g.*
from groups g
where not exists (
select 1
from registry_in_group rig
inner join registry r
on r.id_registry = rig.id_registry
and timestampdiff(year,r.dob,curdate()) < 18
where rig.id_group = g.id_group
)
基于查询的另一种选择是将聚合与having子句一起使用，以过滤出包含小于18岁的注册表的组：
select g.name
from groups g
inner join registry_in_group rig
on g.id_group = rig.id_group
inner join registry r
on rig.id_registry = r.id_registry
group by g.id_group,g.name
having sum( timestampdiff(year,curdate()) < 18 ) = 0
NB：正如Strawberry所评论的那样，group是所有RDBMS中的保留字；我改用groups以避免混淆。