Three ways to write of group by xxx about each user taking 1 record in MYSQL
- 2020-05-27 07:21:47
- OfStack
Today, some students asked me about the SQL statement in this aspect. I specially memorized it for 1, because it is also common
The first way is to sort first, then group, so that you can get the most suitable data.
The disadvantages are obvious: Using temporary; Using filesort
The second is a joint query
The third type is subqueries
I think the second one is the most efficient
select * from (select * from member_payment
order by id desc) t group by member_id limit 10
The first way is to sort first, then group, so that you can get the most suitable data.
The disadvantages are obvious: Using temporary; Using filesort
select s.*
from (SELECT max(id) as id FROM `member_payment` group by `member_id` limit 10) t
left join `member_payment` as s on t.id=s.id
The second is a joint query
select * from `member_payment` where EXISTS (
select `id` from (
SELECT max(`id`) as id FROM `member_payment` group by `member_id` limit 10) t
where t.`id`=`member_payment`.`id`
)
The third type is subqueries
I think the second one is the most efficient