查询数据分组之后的前几条数据 可根据不同情况筛选
需求 : 1)获取每个企业新发布的4个产品
2)VIP企业获取8个产品
简单的demo
这是所有的数据
SELECT
product_name,
company_id,
add_time,
vip,
rn
FROM
(SELECT
product_name,
company_id,
add_time,
vip,
@rn:=CASE
WHEN @var_company_id = company_id THEN @rn + 1
ELSE 1
END AS rn,
@var_company_id:=company_id
FROM ms_products, (SELECT @rn := 0 ,@var_company_id := 0) AS vars
WHERE company_id IN (SELECT company_id FROM ms_products )
ORDER BY company_id ,add_time DESC
) as products
WHERE
((rn <= 4 and vip =0) OR ( rn <= 8 and vip = 1 ))
ORDER BY company_id,add_time DESC
查询结果
其实就是查询的时候增加个自定义字段rn,并根据该字段筛选排除