id uid brand
1 1 bmw
2 1 benz
3 2 honda
4 8 bmw
5 4 bmw
6 4 benz
7 2 benz
8 4 honda
9 8 benz
10 2 bmw
11 7 honda
12 5 benz
13 12 benz 写个查询语句,取道的结果如下:
所有brand的类别的最新的前2个
eg.
10 2 bmw
5 4 bmw
13 12 benz
12 5 benz
11 7 honda
8 4 honda
1 1 bmw
2 1 benz
3 2 honda
4 8 bmw
5 4 bmw
6 4 benz
7 2 benz
8 4 honda
9 8 benz
10 2 bmw
11 7 honda
12 5 benz
13 12 benz 写个查询语句,取道的结果如下:
所有brand的类别的最新的前2个
eg.
10 2 bmw
5 4 bmw
13 12 benz
12 5 benz
11 7 honda
8 4 honda
from qwe a left join qwe b
on a.brand=b.brand and a.id<=b.id
group by a.id,a.uid,a.brand having count(b.id)<=2
from yourTable a inner join yourTable b on a.brand=b.brand and a.id <=b.id
group by a.id,a.uid,a.brand
having count(b.id) <=2 select *
from yourTable a
where 2>=(select count(*) from yourTable where brand=a.brand and id >=a.id )
5 4 bmw
8 4 honda
10 2 bmw
11 7 honda
12 5 benz
13 12 benz 查询出来的结果是这样的,能不能在按类别分下类?
比如:
id uid brand
5 4 bmw
10 2 bmw
8 4 honda
11 7 honda
12 5 benz
13 12 benz 还有就是上边的查询比起做三次
select * from car where brand='bmw' limit 0,2
select * from car where brand='honda' limit 0,2
select * from car where brand='benz' limit 0,2是快还是慢,如果查询数据量比较大的时候,5,6万条的时候
from qwe a left join qwe b
on a.brand=b.brand and a.id <=b.id
group by a.id,a.uid,a.brand having count(b.id) <=2
order by a.brand
where (select count(1)+1 from tb where brand=t.brand and id>t.id)<=2
order by brand/**
id uid brand
----------- ----------- -----
12 5 benz
13 12 benz
5 4 bmw
10 2 bmw
11 7 honda
8 4 honda
**/