如何根据每个用户的给定记录集获取最后 5 或 6 条记录 [英] How to get last 5 or 6 records based on given recordset per user

问题描述

这是对我的问题的更新 如何根据给定的记录集获取最后 5 或 6 条记录.

This is an update to my question How to get last 5 or 6 records based on given recordset.

如果给定记录集的事件值是 2，我需要一个可以获取最后 6 条记录的东西，并且获取最后 5 条记录 id 事件为 1.给定的记录是数据外观的样本就像在我的 MySql 表中一样.

I need a something that will get the last 6 records if the last row in the given record set's event value is 2, and get the last 5 records id the event is 1. The given records are samples of what the data looks like in my MySql table.

表格结构

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

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

Here's my current attempt in solving the said problem:

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

推荐答案

稍微复杂一些.T sub 查询为每个用户分配一个行号，然后用 where 语句决定应该返回哪些行

Slightly more complicated. The T sub query allocates a row number to each user which is then joined to the s sub query with the where statement deciding which rows should be returned

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
;

这篇关于如何根据每个用户的给定记录集获取最后 5 或 6 条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！