mysql - hasMany 关系的潜在复杂查询；这可以做到吗？

Question

一个 Person 可以有很多 Degree 记录。

人物

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(60)  | NO   |     | NULL    |                |
| lastName  | varchar(100) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

学位 (编辑:为简单起见，我隐藏了许多其他字段)

+----------+-------------------------------+------+-----+---------+----------------+
| Field    | Type                          | Null | Key | Default | Extra          |
+----------+-------------------------------+------+-----+---------+----------------+
| id       | int(11)                       | NO   | PRI | NULL    | auto_increment |
| personID | int(11)                       | NO   | MUL | NULL    |                |
| type     | enum('PhD','MSE','MEng','MA') | NO   |     | NULL    |                |
+----------+-------------------------------+------+-----+---------+----------------+

在网页上，我想显示人员列表和他们的学位信息(除了他们的类型之外还有其他学位信息，为了简单起见我没有包括它)。我相信这必须在 N+1 查询中完成。首先，查询选择所有拥有学位的人:

SELECT DISTINCT person.id
FROM person
JOIN degree ON degree.personID = person.id

然后在遍历每个结果时，直接查询它们的度数:

SELECT * FROM degree WHERE personID = :personID

然而，我的生活并没有那么简单。我有两个额外的要求:

1. 允许根据程度过滤页面。例如:“只显示拥有‘MA’学位的人”、“只显示拥有‘MSE’学位的人”。
2. 如果此人没有“博士学位”学位，则仅显示“硕士学位”。

我正在努力解决如何将这些要求合并到查询中的问题。对于第一个需求，主查询需要事先知道用户拥有什么学位。这里需要子查询吗？

规则还需要以某种方式参与主查询(以及加载度数的第二个查询)。

Answer 1

您不需要为此进行多次查询。我假设您想要列表中的学位，例如:

SELECT p.id, group_concat(d.type)
FROM person p JOIN
     degree d
     ON d.personID = p.id
GROUP BY p.id

对于过滤，您可以使用having。例如，以下将始终返回博士学位:

HAVING sum(d.degree = 'phd') > 0

“MA”/“PhD”问题增加了一些复杂性:

SELECT p.id,
       (case when sum(d.degree = 'phd') > 0
             then group_concat(case when d.type <> 'ma' then d.type end)
             else group_concat(d.type)
        end) as degrees
FROM person p JOIN
     degree d
     ON d.personID = p.id
GROUP BY p.id