我在数据库中有此数据:
musterid | musterdate | userid | status
---------+---------------------+----------+-------
69 | 2017-09-01 11:58:37 | 50 | 1
70 | 2017-09-01 12:00:07 | 50 | 1
71 | 2017-09-01 12:29:15 | 50 | 1
72 | 2017-09-01 12:31:15 | 50 | 1
73 | 2017-09-01 09:58:37 | 51 | 1
74 | 2017-09-01 11:00:07 | 51 | 1
75 | 2017-09-01 12:29:15 | 51 | 1
76 | 2017-09-01 07:31:15 | 51 | 1
我想要这样的输出:
musterid | musterdate | userid | status
---------+---------------------+----------+-------
69 | 2017-09-01 11:58:37 | 50 | 1
72 | 2017-09-01 12:31:15 | 50 | 1
73 | 2017-09-01 09:58:37 | 51 | 1
76 | 2017-09-01 07:31:15 | 51 | 1
即。我想为每个用户获取第一个和最后一个入门时间。
如何实现这一目标?
我不知道如何创建表代表数据,所以对我的演示表示抱歉。
复杂查询要获取每个用户的第一个和最后一个输入时间":
SELECT
t1.*
FROM
yourtable t1
LEFT JOIN
(SELECT
MIN(musterdate) AS min_mdate,
MAX(musterdate) AS max_mdate,
userid
FROM
yourtable
GROUP BY userid) t2 ON t1.userid = t2.userid
WHERE
t1.userid = t2.userid
AND t1.musterdate IN (t2.min_mdate , t2.max_mdate)
ORDER BY userid , musterdate;