mysql - 多个连接操作后按 Count(*) 分组

Question

试图弄清楚我认为是一个简单的查询-

我有下表，其架构如下。

models(id: int, first_name: string, last_name: string)

painters(id: int, first_name: string, last_name:string)

portraits_painters(painter_id: int, portrait_id: int)

roles(model_id: int, portrait_id: int)

我正在尝试满足此查询:名字、姓氏和计数对于曾作为自己绘画模特的画家 - 即名字和姓氏的计数列是画家完成的自画像的数量(画家和模特)。 (可以安全地假设相同的名字和姓氏表示同一个人)。

如果画家没有自画像，那么它们甚至不应该包含在最终输出中。

SELECT
  models.first_name,
  models.last_name,
  COUNT(portraits_painters.portrait_id)
FROM models
RIGHT JOIN painters
  ON models.first_name = painters.first_name
  AND models.last_name = painters.last_name
RIGHT JOIN portraits_painters
  ON portraits_painters.painter_id = painters.id
RIGHT JOIN roles
  ON roles.portrait_id = portraits_painters.portrait_id
GROUP BY portraits_painters.portrait_id;

我本质上试图将表格缩小到名称匹配的行，然后是画家 id 匹配和肖像 id 匹配的实例，但显然我连接表格的方式有问题。我还收到有关 GROUP BY 部分的错误，因此不确定是否有更好的方法来处理它？也许是别名？

Answer 1

您可以使用以下方式获取模特和画家的姓名:

select m.first_name, m.last_name, p.first_name, p.last_name
from roles r join
     portraits_painters pp
     on pp.portrait_id = r.portrait_id join
     painters p
     on p.id = pp.painter_id join
     models m
     on m.id = r.model_id;

剩下的只是一个where和group by:

select p.first_name, p.last_name, count(*)
from roles r join
     portraits_painters pp
     on pp.portrait_id = r.portrait_id join
     painters p
     on p.id = pp.painter_id join
     models m
     on m.id = r.model_id
where m.first_name = p.first_name and m.last_name = p.last_name
group by p.first_name, p.last_name;

我不确定什么想法会导致外部联接或 exists 子句。