mysql - 根据特定条件加入 3 个表

Question

我有以下 3 个表：

• 用户：[id, name, admin ...]
• 事件：[id, user_id, type ...]
• 消息：[id, user_id, ...]

我想构建一个执行以下操作的查询：

-> 从表用户中选择所有未安排“集合”类型事件的用户

-> 并且谁拥有少于 3条“collection_reminder” 类型的消息

-> 谁不是管理员

我已经设法弄清楚了这个查询的第一部分，但是当我尝试添加 3 个表、进行计数等时，这一切都变得有点梨形了。

标签： mysqlsql

Answer 1

This query uses joins to link the 3 tables, filters the result using the where clause, and using Group by, having limiting the result to only those who satisfy the less than count condition..

SELECT    a.id, 
      SUM(CASE WHEN b.type = 'collection' THEN 1 ELSE 0 END),
      SUM(CASE WHEN c.type = 'collection_reminder' THEN 1 ELSE 0 END
FROM      users a 
left join   events b on (b.user_id = a.id)
left join   messages c on (c.user_id = a.id)
WHERE     a.admin = false
GROUP BY  a.id
HAVING  SUM(CASE WHEN b.type = 'collection' THEN 1 ELSE 0 END) = 0 
    AND SUM(CASE WHEN c.type = 'collection_reminder' THEN 1 ELSE 0 END) < 3