mysql 分组且每组分页

最新推荐文章于 2024-07-20 08:59:24 发布

wanhf11

最新推荐文章于 2024-07-20 08:59:24 发布

阅读量1.2k

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/qq_17612199/article/details/50849411

版权

Mysql 专栏收录该内容

45 篇文章 2 订阅

订阅专栏

CREATE TABLE `mygoods` (  
  `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  `cat_id` int(11) NOT NULL DEFAULT '0',  
  `price` tinyint(3) NOT NULL DEFAULT '0',  
  `status` tinyint(3) DEFAULT '1',  
  PRIMARY KEY (`goods_id`),  
  KEY `icatid` (`cat_id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;  

INSERT INTO `mygoods` VALUES (1, 101, 90, 0);  
INSERT INTO `mygoods` VALUES (2, 101, 99, 1);  
INSERT INTO `mygoods` VALUES (3, 102, 98, 0);  
INSERT INTO `mygoods` VALUES (4, 103, 96, 0);  
INSERT INTO `mygoods` VALUES (5, 102, 95, 0);  
INSERT INTO `mygoods` VALUES (6, 102, 94, 1);  
INSERT INTO `mygoods` VALUES (7, 102, 93, 1);  
INSERT INTO `mygoods` VALUES (8, 103, 99, 1);  
INSERT INTO `mygoods` VALUES (9, 103, 98, 1);  
INSERT INTO `mygoods` VALUES (10, 103, 97, 1);  
INSERT INTO `mygoods` VALUES (11, 104, 96, 1);  
INSERT INTO `mygoods` VALUES (12, 104, 95, 1);  
INSERT INTO `mygoods` VALUES (13, 104, 94, 1);  
INSERT INTO `mygoods` VALUES (15, 101, 92, 1);  
INSERT INTO `mygoods` VALUES (16, 101, 93, 1);  
INSERT INTO `mygoods` VALUES (17, 101, 94, 0);  
INSERT INTO `mygoods` VALUES (18, 102, 99, 1);  
INSERT INTO `mygoods` VALUES (19, 105, 85, 1);  
INSERT INTO `mygoods` VALUES (20, 105, 89, 0);  
INSERT INTO `mygoods` VALUES (21, 105, 99, 1);

每个分类找出价格最高的两个商品

SELECT
    a.*
FROM
    mygoods a
WHERE
    (
        SELECT
            count(*)
        FROM
            mygoods b
        WHERE
            b.cat_id = a.cat_id
        AND b.price > a.price
    ) < 2
AND a. STATUS = 1
ORDER BY
    a.cat_id ASC,
    a.price DESC

2   101 99  1
18  102 99  1
8   103 99  1
9   103 98  1
11  104 96  1
12  104 95  1
21  105 99  1