找到给定三个ID(500,501,502(的每个ID的最后10个记录
假设我有一个像这样的表
id | user_id | rating
1 | 500 | 5
2 | 501 | 3
3 | 500 | 5
4 | 502 | 4
5 | 502 | 1
如何编写MySQL查询以通过ID DESC
假设您的ID是自动增量列,而三个ID(500,501,502(适用于User_id那么您可以使用
select *
from my_table
where user_id in (500, 501,502)
order by id desc
limit 10
有点懒惰,这是一种通过使用变量分配一个行连接到每个用户的最大出现
来获取每个user_id最后2个的方法DROP TABLE IF EXISTS T;
CREATE TABLE T
(ID INT AUTO_INCREMENT PRIMARY KEY, USER_ID INT, RATING INT);
INSERT INTO T (USER_ID,RATING) VALUES
(500,1),
(502,1),
(500,2),(500,3),
(502,2),
(600,1);
SELECT U.*
FROM
(
SELECT S.ID,S.USER_ID,S.RATING,
T.MAXROWS
FROM
(
SELECT ID,USER_ID,RATING,
IF(USER_ID <> @P,@R:=1,@R:=@R+1) RN,
@P:=USER_ID
FROM T
ORDER BY USER_ID ASC,ID ASC
) S
JOIN
(SELECT USER_ID,COUNT(*) MAXROWS FROM T GROUP BY USER_ID) T ON T.USER_ID = S.USER_ID
) U
WHERE U.RATING > U.MAXROWS - 2 AND U.USER_ID IN(500,502);
+----+---------+--------+---------+
| ID | USER_ID | RATING | MAXROWS |
+----+---------+--------+---------+
| 3 | 500 | 2 | 3 |
| 4 | 500 | 3 | 3 |
| 2 | 502 | 1 | 2 |
| 5 | 502 | 2 | 2 |
+----+---------+--------+---------+
4 rows in set (0.00 sec)
您可以使用联合所有操作员获得每个ID
的10个结果(select * from test
where user_id =500
order by id desc
limit 10)
UNION ALL
(select * from test
where user_id =501
order by id desc
limit 10)
UNION ALL
(select * from test
where user_id =502
order by id desc
limit 10)
order by id desc;
在此处查看Demo
注意:欢迎审稿人增强或改进查询。