mysql中GROUP BY分组取前N条记录实现

https://blog.csdn.net/yiluoAK_47/article/details/44591671

CREATE TABLE `ips_favorite_product` (

`id`bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',

`favorite_id`bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '收藏夹id',

  `sku` varchar(100) NOT NULL DEFAULT '' COMMENT '产品编码',

  `spu` varchar(100) NOT NULL DEFAULT '' COMMENT '商品编码',

  `website_code` varchar(20) NOT NULL DEFAULT '' COMMENT '网站编码',

  `description` varchar(255) NOT NULL DEFAULT '' COMMENT '描述',

  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1.启用 2.删除',

  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',

  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',

  `create_user` varchar(30) NOT NULL DEFAULT '' COMMENT '创建人用户名',

  `update_user` varchar(30) NOT NULL DEFAULT '' COMMENT '更新人用户名',

  PRIMARY KEY (`id`),

  UNIQUE KEY `uniq_favorite_id_sku` (`favorite_id`,`sku`) USING BTREE,

  KEY `idx_create_time` (`create_time`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='收藏夹具体产品';

一.

SELECT a.* FROM ips_favorite_product a LEFT JOIN ips_favorite_product b ON  a.favorite_id=b.favorite_id and a.id>b.id where a.favorite_id in (1,2) group by a.id,a.favorite_id having count(b.id)<2 ORDER BY a.favorite_id asc;

二.

select a.* from ips_favorite_product a where  a.favorite_id in (1,2,3) and 3 > (select count(*) from ips_favorite_product where favorite_id = a.favorite_id and id > a.id ) order by a.favorite_id,a.id;