mysql获取每个分类下⾯的前五条数据
现在项⽬遇到个问题，我电商⽹站，商品有很多分类，我想取出每个分类下⾯的前五条数据，应该怎么做呢？
数据结构如下：
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张',
蜡笔crayon发音`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;
邀请回答
编辑
6个回答
transform 词组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
divcss样式大全ds_ds_name,a.cate_id,count(*) as num from goods as a
inner join goods as b on a.cate_id=b.cate_id ds_id>=b.goods_id
group ds_id having num<=5 order by a.cate_id ds_id asc
//注：⼀个表⾃⼰内连接⾃⼰，使其cate_id对等，且a.goods_id>=b.goods_id。最后group ds_id having num<=5
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
jpa有哪些国家FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 5
ORDER BY a.class_pro_id ASC, a.id DESC;
3.
select a.*,count(*) as num from products as a inner join products as b ON a.class_pro_id=b.class_pro_id
where b.id<=a.id group by a.id having num<=2;
group_concat得到每组⽤逗号分隔的列值，最⼤长度可以通过group_concat_max_len环境变量设置。
find_in_set返回id在ids中的位置，不存在返回0。
编辑
160 声望
1
是题主表述不清吗？我觉得挺清楚的啊，下⾯⼏位完全跑题！@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,
id,name,...
from products a ,(select @gn:=1) b
order by class_pro_id,id) aa
where gn<=5;
select @cod:=case when @cate_id=cate_id then @cod+1 else 1 end cod,
@cate_id:=cate_id,goods_id,goods_name,cate_id from goods as a,
(select @cod:=0) as b order by cate_id asc,goods_id asc
//对于SET，可以使⽤=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。
也可以⽤语句代替SET来为⽤户变量分配⼀个值。在这种情况下，分配符必须为:=⽽不能⽤=，因为在⾮SET语句中=被视为⼀个⽐较操作符：
select goods_id,goods_name,cate_id,@a:= case when @cid=cate_id then @a+1 else 1 end as num,ifnull(@a,@a:=0),@cid:=cate_id from goods order by cate_id asc, goods_id asc
//注：如果变量@a没有被设置过会返回null,⽤ifnull(@a,@a:=0) 这样可以设置变量@a=0 初始化变量
SELECT name, '分类1' AS type_name FROM products WHERE class_pro_id = 1 LIMIT 5
UNION
mysql语句分类>手游源码端游源码页游源码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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论