项目遇到个问题,我电商网站,商品有很多分类,我想取出每个分类下面在架的前五条数据,并且按照sort升序排列,应该怎么做呢?
数据结构如下:
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;
直接上结果:
SELECT
a.*
FROM
products AS a,
(SELECT
GROUP_CONCAT(id order by id desc) AS ids
FROM
products where status='up'
GROUP BY class_pro_id) AS b
WHERE
FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 3
这个是我之前再segment上提的问题,现在小结一下:
https://segmentfault.com/q/1010000011404345/a-1020000011412416
还有个博客:
https://blog.csdn.net/dotalee/article/details/50240399