如何根据每个用户的给定记录集获取最后 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屋!