mysql获取每个分类下面的前五条数据

Question

现在项目遇到个问题，我电商网站，商品有很多分类，我想取出每个分类下面的前五条数据，应该怎么做呢？
数据结构如下：

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL COMMENT '商品名称，65字以内',
  `class_pro_id` mediumint(8) unsigned NOT NULL DEFAULT 0 COMMENT '商品分类id',
  `description` varchar(600) COLLATE utf8_unicode_ci NOT NULL COMMENT '商品描述，200字以内',
  `present_price` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT '现价',
  `original_price` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT '原价',
  `status` enum('up','down') CHARACTER SET utf8 NOT NULL DEFAULT 'up' COMMENT '商品状态，down表示下架，up表示在架',
  `image` text COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '商品主图，最多6张',
  `content` text COLLATE utf8_unicode_ci NOT NULL COMMENT '商品详情',
  `sort` mediumint(9) NOT NULL DEFAULT 0 COMMENT '商品排序，越大越靠前',
  `taobao_url` varchar(300) COLLATE utf8_unicode_ci NOT NULL COMMENT '淘宝链接',
  `weixin_url` varchar(300) COLLATE utf8_unicode_ci NOT NULL COMMENT '微信链接',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

1.

SELECT 
    a.*
FROM
    products AS a
WHERE
    (SELECT 
            COUNT(*)
        FROM
            products AS b
        WHERE
            b.class_pro_id = a.class_pro_id AND b.id >= a.id) <= 5
ORDER BY a.class_pro_id ASC , a.id DESC

2.

SELECT 
    a.*
FROM
    products AS a,
    (SELECT 
        GROUP_CONCAT(id order by id desc) AS ids
    FROM
        products
    GROUP BY class_pro_id) AS b
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 5
ORDER BY a.class_pro_id ASC, a.id DESC;

group_concat得到每组用逗号分隔的列值，最大长度可以通过group_concat_max_len环境变量设置。
find_in_set返回id在ids中的位置，不存在返回0。

Answer 2

是题主表述不清吗？我觉得挺清楚的啊，下面几位完全跑题！@yaohuiye这位还搭点边，可是差个排序，即使是按照id顺序取值也应该加上才好，还有这写法简直是灾难！每组取前5条，不是取5个组就好了
mysql可以引入组内行号解决

select id,name,class_pro_id,... 
from (
    select 
        @gn:=case when @class_pro_id=class_pro_id then @gn+1 else 1 end gn,
        @class_pro_id:=class_pro_id class_pro_id,
        id,name,...
    from products a ,(select @gn:=1) b 
    order by class_pro_id,id) aa
where gn<=5;    

Answer 3

SELECT name, '分类1' AS type_name FROM products WHERE class_pro_id = 1 LIMIT 5
UNION
SELECT name, '分类2' AS type_name FROM products WHERE class_pro_id = 2 LIMIT 5
UNION
SELECT name, '分类3' AS type_name FROM products WHERE class_pro_id = 3 LIMIT 5

Answer 4

select b.* from 
    (select id, class_pro_id from products group by id, class_pro_id limit 5) a,
    products b
where 1 = 1
    and a.id = b.id
    and a.class_pro_id = b.class_pro_id;

Answer 5

之前有遇到过，看博客，MySQL分组排序查前N条数据

Answer 6

只说一下大概方案啊，先按照分组，然后limit就好了，楼上所说，也不错，不过就是写得多了点

Answer 7

分组问题确实比较麻烦，最近也在考虑着这么写，结果想着想着发现这没想象中简单。
参考了https://blog.csdn.net/dotalee...