分组取每组前几条数据

    有个需求：列出每个商家的前几个货品，并展示图片

    其中：用户表：t_user，商家表：t_merchant，货品表：t_product,   图片表：t_product_file

    看似简单的一个需求，但是用其他方式查询效率不高，并且一次性拿出数据，当数据量比较大时，查询很慢。

    在此提供一种较好的方式：

-- 取每组前N条记录：（取每个店铺goods_id最大的前3条）
set @rank = 0, @tmp = '';
SELECT goods_id,shop_id,title, newCount as count from 
(
	SELECT goods_id,shop_id,title, 
	IF(@tmp=shop_id,@rank:=@rank + 1,@rank:=1) as newCount,
	@tmp:=shop_id as tmp
	FROM t_goods
	ORDER BY shop_id, goods_id DESC) b
where newCount <= 3;

    

以下为特殊情况的方法：

select
		m.merchant_id,
		u.user_id,
		u.nick_name,
		m.merchant_name,
		m.delivery_address,
		m.logo,
		IFNULL(floor(pm.merchant_level/pm.comment_count),0) merchantScore,
		JSON_EXTRACT(
		      CONCAT('[',
		          substring_index(
		                GROUP_CONCAT(
		                      JSON_OBJECT(
									'productId',p.product_id,
									'goodsId',p.goods_id,
									'productName',p.product_name,
									'marketPrice',p.market_price,
									'mallPrice',p.mall_price,
									'vb',p.vb,
									'productFile',pf.file,
									'jumpUrl',p.jump_url,
									'sale_num',p.sale_num
									)
		                      ORDER BY p.sale_num DESC separator ','
		                )
		          ,',{',3)
		      ,']')
		,'$[0]','$[1]','$[2]') jsonProducts
		from t_product p
		join t_merchant m on m.merchant_id=p.merchant_id
		join t_user u on u.user_id=m.user_id
		left join (select product_id,file from t_product_file where default_status=true GROUP BY product_id) pf on pf.product_id=p.product_id
		left join (select merchant_id,sum(merchant_level) merchant_level,count(product_comments_id) comment_count from t_product_comments where comments_type='comments' GROUP BY merchant_id) pm on pm.merchant_id=p.merchant_id
		where 1=1
		and m.`status`=true
		and m.seal_status=false
		and m.audit_status='succe'
		and	p.shelves_status=1 
		AND p.audit_status='succe' 
		AND p.`status`=1
		AND m.merchant_name LIKE CONCAT('%', #{key},'%')
		GROUP BY m.merchant_id
		order by pm.merchant_level/pm.comment_count desc
		limit #{offset},#{pageSize}