MYSQL:检索最新的唯一记录 [英] MYSQL: Retrieve latest unique records
本文介绍了MYSQL:检索最新的唯一记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个表，该表记录了member_id重复的行.
I have a table that logs rows where the member_id repeats.
id: int(7)主键，自动递增
member_id: int(7)索引
item_qt: int(7)
id: int(7) Primary key, Auto increment
member_id: int(7) Index
item_qt: int(7)
+----+-----------+---------+----------+
| id | member_id | item_qt |timestamp |
+----+-----------+---------+----------+
|553 | 107 | 10 | 00:17:53 |
+----+-----------+---------+----------+
|552 | 107 | 25 | 00:17:51 |
+----+-----------+---------+----------+
|551 | 122 | 6 | 00:17:43 |
+----+-----------+---------+----------+
|550 | 122 | 9 | 00:17:22 |
+----+-----------+---------+----------+
|549 | 107 | 19 | 00:17:11 |
+----+-----------+---------+----------+
- 选择最新的唯一行
(这篇文章给了我一些指导) >
(This post gave me some direction) Retrieve last (latest) distinct highest value
+----+-----------+---------+----------+
| id | member_id | item_qt |timestamp |
+----+-----------+---------+----------+
|553 | 107 | 10 | 00:17:53 |
+----+-----------+---------+----------+
|551 | 122 | 6 | 00:17:43 |
+----+-----------+---------+----------+
+----+-----------+---------+----------+-------------+
| id | member_id | item_qt |timestamp | item_qt_sum |
+----+-----------+---------+----------+-------------+
|553 | 107 | 10 | 00:17:53 | 54 |
+----+-----------+---------+----------+-------------+
|551 | 122 | 6 | 00:17:43 | 15 |
+----+-----------+---------+----------+-------------+
- 将item_qt求和的行数小于< 5分钟的时间
推荐答案
SELECT *
FROM table t
JOIN ( SELECT MAX( id ) AS id, SUM( item_qt ) AS sum_item_qt
FROM table
WHERE `timestamp` >= DATE_SUB( NOW( ) , INTERVAL 5
MINUTE )
GROUP BY member_id
)t2 ON t2.id = t.id
ORDER BY t.id DESC
LIMIT 10
这篇关于MYSQL:检索最新的唯一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
