mysql - 如何根据每个用户给定的记录集获取最后 5 或 6 条记录

Question

这是我的问题 How to get last 5 or 6 records based on given recordset 的更新.

如果给定记录集的事件值的最后一行是 2，我需要一个能够获取最后 6 条记录的东西，并且获取事件 ID 为 1 的最后 5 条记录。给定的记录是数据外观的示例就像我的 MySql 表中一样。

表结构

 CREATE TABLE `timelog` (
  `timeLogId` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(256) NOT NULL,
  `event` int(1) NOT NULL,
  PRIMARY KEY (`timeLogId`)
  )

这是我目前解决上述问题的尝试:

SELECT timeLogId, event FROM (SELECT * FROM timelog ORDER BY timeLogId DESC LIMIT 6) as T ORDER BY timeLogId ASC

记录集1

timeLogId | user    |  event
1         | bigfish |      1
2         | catThum |      1
3         | dogBark |      1
4         | bigfish |      2
5         | catThum |      2
6         | dogBark |      2
7         | bigfish |      1
8         | catThum |      1
9         | dogBark |      1
10        | bigfish |      2
11        | catThum |      2
12        | dogBark |      2
13        | bigfish |      1
14        | catThum |      1
15        | dogBark |      1
16        | bigfish |      2
17        | catThum |      2
18        | bigfish |      1

结果:

 timeLogId | user    |  event
 7         | bigfish |      1
 10        | bigfish |      2
 13        | bigfish |      1
 16        | bigfish |      2
 18        | bigfish |      1

记录集2

timeLogId | user    |  event
1         | bigfish |      1
2         | catThum |      1
3         | dogBark |      1
4         | bigfish |      2
5         | catThum |      2
6         | dogBark |      2
7         | bigfish |      1
8         | catThum |      1
9         | dogBark |      1
10        | bigfish |      2
11        | catThum |      2
12        | dogBark |      2
13        | bigfish |      1
14        | catThum |      1
15        | dogBark |      1
16        | bigfish |      2
17        | catThum |      2
18        | bigfish |      1
19        | catThum |      1
20        | dogBark |      1
21        | bigfish |      2

结果:

timeLogId | user    |  event
7         | bigfish |      1
10        | bigfish |      2
13        | bigfish |      1
16        | bigfish |      2
18        | bigfish |      1
21        | bigfish |      2

Answer 1

稍微复杂一点。 T 子查询为每个用户分配一个行号，然后将其连接到 s 子查询，并使用 where 语句决定应返回哪些行

SELECT T.timelogid,t.user,t.event
FROM
(
SELECT T1.TIMELOGID, t1.user,T1.EVENT
FROM    TIMELOG T1
WHERE T1.TIMELOGID = (SELECT MAX(TIMELOGID) FROM TIMELOG)
) S
JOIN 
(
select t2.*,if(user<>@p , @rn:=1,@rn:=@rn+1) rn,@p:=user p 
from (select @rn:=0,@p:='') rn,TIMELOG T2 
order by user ,timelogid desc
) t
on t.user = s.user 
where t.rn <=
    CASE 
    WHEN S.EVENT = 1 THEN  5
    WHEN S.EVENT = 2 THEN  6
    END
order by t.timelogid
;