我有三个表,我需要找到用户作为主人和客人加入的活动的第一个日期和最后一个(最近)日期。换句话说,返回的数据应该如下所示:
(请注意,这是用户参加的所有事件中的第一个也是最后一个(最近)日期)
+----+-----+---------+----------------------+-----------------------+-----------------------+-----------------------+
| id |name | Event | First date as a host | Recent date as a host | First date as a guest | Recentdate as a guest |
+----+-----+---------+----------------------+-----------------------+-----------------------+-----------------------+
| 1 | Teo | Dance | 2018-01-01 | 2017-01-01 | 2016-02-01 | 2017-09-09 |
| 2 | Jac | Karaoke | 2018-03-02 | 2018-01-01 | 2016-07-07 | 2017-01-06 |
| 3 | Lay | Boxing | 2017-09-09 | 2018-07-07 | 2017-01-01 | 2018-02-02 |
+----+-----+---------+----------------------+-----------------------+-----------------------+-----------------------+
表格
用户
id
名字
事件
id
名字
starts_at
Event_Users
id
user_id
event_id
角色
我试着遵循这里的指示[https://www.periscopedata.com/blog/4-ways-to-join-only-the-first-row-in-sql]..。但是,当涉及到编写一个检索4个不同的第一行(min/max)的查询时,我总是得到一个超级混乱的查询,甚至不能工作……
对于这种棘手的需求,最简单的解决方案是什么?
谢谢
发布于 2018-06-07 04:40:41
除了使用MIN
和MAX
而不是SUM
之外,它遵循与need to return two sets of data with two different where clauses相同的模式。
SELECT u.id, u.name, e.name AS Event,
MIN(IF(eu.role = 'host', e.starts_at, NULL)) AS First_date_as_host,
MAX(IF(eu.role = 'host', e.starts_at, NULL)) AS Recent_date_as_host,
MIN(IF(eu.role = 'guest', e.starts_at, NULL)) AS First_date_as_guest,
MAX(IF(eu.role = 'guest', e.starts_at, NULL)) AS Recent_date_as_guest
FROM users AS u
JOIN event_users AS eu ON eu.user_id = u.id
JOIN events AS e ON e.id = eu.event_id
GROUP BY u.id, e.id
https://stackoverflow.com/questions/50728947
复制相似问题