Complex mysql query. Details under the cut

Question

MySQL

There is a table with 3 fields
id - primary key
Action - several predefined values
​​\u200b\u200bof user_id - user id, number

You need to select 20 records, but so if consecutive rows have the same user_id and action, then no matter how many of them go in a row, they are considered for one entry. That is, we combine consecutive rows with the same user_id, action into one content unit.

It only comes to mind to choose 20 pieces and, if necessary, merge in the controller. But it's not very pretty :(

Answer 1

select id, action, user_id
from (select ifnull(@a<>action or @b<>user_id, 1) as w, id, @a:=action as action, @b:=user_id as user_id from table1 order by id) as t
where w

Or a simplified version. If ID is a direct primary key, then no sorting is needed, and therefore no subquery is needed. Input Id, action , user_id 100 1 1 101 1 1 102 1 2 103 1 1 104 1 2 Output id action user_id 100 1 1 102 1 2 103 1 1 104 1 2
select * from table1 where (@b:=user_id)+(@a:=action)+(@w:=ifnull(@a<>action or @b<>user_id, 1))+1 and @w


Answer 2

Maybe I misunderstood, but isn't it?
SELECT `id`, `Action`, `user_id` FROM `table` GROUP BY `Action`, `user_id` LIMIT 20;
or
SELECT `id`, `Action`, `user_id` FROM (SELECT * FROM `table` LIMIT 20) GROUP BY `Action`, `user_id`;

Answer 3

SELECT `id`, `action`, `user_id` FROM `table` a
WHERE 
(SELECT MAX(b.`id`) FROM table b WHERE b.`id` < a.`id`) 
!= 
(SELECT MAX(c.`id`) FROM `table` c WHERE c.`action`=a.`action` AND c.`user_id` = a.`user_id` AND c.`id` < a.`id`)

Maybe I made a mistake somewhere, or it can be simpler, but the idea, I think, is clear ...

Answer 4

If you have data like

id, action, user_id
100 1 1
101 1 1
102 1 2
103 1 1
104 1 2

, then obviously you don't need the Id column because it doesn't make sense. You only need action, user_id.
Then it will be SELECT DISTINCT action, user_id FROM table ORDER BY user_id LIMIT 120,10;all there.