分组排列，每组前几名sql

 

 

create table `shop` (
    `id` int (10) PRIMARY KEY,
    `shop_name` varchar (100),
    `item_name` varchar (100),
    `price` int (10)
); 

 

 

每个shop 中价格最高的前N 条数据

select * from shop a where N > (select count(*) from shop b where b.shop_name = a.shop_name and  b.price<a.price) order by a.shop_name,a.price desc;

每个shop 中价格最第的前N 条数据

select * from shop a where N > (select count(*) from shop b where b.shop_name = a.shop_name and  b.price>a.price) order by a.shop_name,a.price desc;