mysql按日期为每个用户选择最新的行

debugcn 发表于 Dev

ctown4life

我正在尝试为pid = 50和active = 1的每个唯一用户ID选择最新的行。

这是一个示例表

+-----+----------+-------+-----------------------+---------+
| id  | userid   | pid   | start_date            | active  |
+-----+----------+-------+-----------------------+---------+
| 1   | 4        | 50    | 2015-05-15 12:00:00   | 1       |
| 2   | 4        | 50    | 2015-05-16 12:00:00   | 1       |
| 3   | 4        | 50    | 2015-05-17 12:00:00   | 0       |
| 4   | 4        | 51    | 2015-06-29 12:00:00   | 1       |
| 5   | 4        | 51    | 2015-06-30 12:00:00   | 1       |
| 6   | 5        | 50    | 2015-07-05 12:00:00   | 1       |
| 7   | 5        | 50    | 2015-07-06 12:00:00   | 1       |
| 8   | 5        | 51    | 2015-07-08 12:00:00   | 1       |
+-----+----------+-------+-----------------------+---------+

所需结果

+-----+----------+-------+-----------------------+---------+
| id  | userid   | pid   | start_date            | active  |
+-----+----------+-------+-----------------------+---------+
| 2   | 4        | 50    | 2015-05-16 12:00:00   | 1       |
| 7   | 5        | 50    | 2015-07-06 12:00:00   | 1       |
+-----+----------+-------+-----------------------+---------+

我已经尝试了很多方法，这是我所获得的最接近的方法，但是不幸的是它并没有退出。

SELECT *
FROM mytable t1
WHERE
    (
        SELECT COUNT(*)
        FROM mytable t2
        WHERE
            t1.userid = t2.userid           
            AND t1.start_date < t2.start_date
    ) < 1
AND pid = 50
AND active = 1
ORDER BY start_date DESC

Amdixon

计划

通过pid获取用户ID的最后记录分组，其中pid为50并且处于活动状态

内部联接到mytable以获取与last相关的记录信息

询问

select 
my.*
from
(
select userid, pid, active, max(start_date) as lst
from mytable
where pid = 50
and   active = 1
group by userid, pid, active
) maxd
inner join mytable my
on  maxd.userid = my.userid
and maxd.pid    = my.pid
and maxd.active = my.active
and maxd.lst    = my.start_date
;

输出

+----+--------+-----+------------------------+--------+
| id | userid | pid |       start_date       | active |
+----+--------+-----+------------------------+--------+
|  2 |      4 |  50 | May, 16 2015 12:00:00  |      1 |
|  7 |      5 |  50 | July, 06 2015 12:00:00 |      1 |
+----+--------+-----+------------------------+--------+

sqlfiddle

笔记

如@Strawberry所建议，已更新为同时加入pid和active。这样可以避免记录处于非活动状态或非pid 50，但也呈现完全相同的日期的可能性。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。