2019 六月 19 , 星期三
Home / 数据存储 / mysql / mysql分组取每组前几条记录(排序)

mysql分组取每组前几条记录(排序)

 

– —————————-
– Table structure for mygoods
– —————————-
DROP TABLE IF EXISTS `mygoods`;
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 AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

– —————————-
– Records of mygoods
– —————————-
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′);

说明:

表mygoods为商品表,cat_id为分类id,goods_id为商品id,status为商品当前的状态位(1:有效,0:无效)。

需求:每个分类下,找出两个价格最高的有效的商品。

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

mysql> select a.*   
    -> from mygoods a   
    -> where (select count(*) 
    -> from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 
    -> order by a.cat_id,a.price desc;
+———-+——–+——-+——–+
| goods_id | cat_id | price | status |
+———-+——–+——-+——–+
|        2 |    101 |    99 |      1 |
|       17 |    101 |    94 |      0 |
|       18 |    102 |    99 |      1 |
|        3 |    102 |    98 |      0 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       20 |    105 |    89 |      0 |
+———-+——–+——-+——–+
10 rows in set (0.00 sec)

2.每个分类找出价格最高的有效的两个商品(正确)

mysql> select a.* 

    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price and status=1  ) <2 
    -> and status=1 
    -> order by a.cat_id,a.price desc ;
+———-+——–+——-+——–+
| goods_id | cat_id | price | status |
+———-+——–+——-+——–+
|        2 |    101 |    99 |      1 |
|       16 |    101 |    93 |      1 |
|       18 |    102 |    99 |      1 |
|        6 |    102 |    94 |      1 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       19 |    105 |    85 |      1 |
+———-+——–+——-+——–+
10 rows in set (0.00 sec)
3.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.* 

    -> from mygoods a 
    -> left join mygoods b 
    -> on a.cat_id = b.cat_id and a.price < b.price and b.status=1
    -> where a.status=1
    -> group by a.goods_id,a.cat_id,a.price
    -> having count(b.goods_id) < 2
    -> order by a.cat_id,a.price desc;
+———-+——–+——-+——–+
| goods_id | cat_id | price | status |
+———-+——–+——-+——–+
|        2 |    101 |    99 |      1 | 
|       16 |    101 |    93 |      1 | 
|       18 |    102 |    99 |      1 | 
|        6 |    102 |    94 |      1 | 
|        8 |    103 |    99 |      1 | 
|        9 |    103 |    98 |      1 | 
|       11 |    104 |    96 |      1 | 
|       12 |    104 |    95 |      1 | 
|       21 |    105 |    99 |      1 | 
|       19 |    105 |    85 |      1 | 
+———-+——–+——-+——–+
10 rows in set (0.00 sec)
4.每个分类找出价格最高的有效的两个商品(错误)
mysql> select a.* 
    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 and status=1 
    -> order by a.cat_id,a.price desc;
+———-+——–+——-+——–+
| goods_id | cat_id | price | status |
+———-+——–+——-+——–+
|        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 |
+———-+——–+——-+——–+
7 rows in set (0.00 sec)
由上可知,如果需要增加条件的话,需要在两处增加条件。

可以将每个分组下的goods_id合并。

mysql> select cat_id,GROUP_CONCAT(goods_id) from mygoods group by cat_id;
+——–+————————+
| cat_id | GROUP_CONCAT(goods_id) |
+——–+————————+
|    101 | 1,2,15,16,17           |
|    102 | 3,5,6,7,18             |
|    103 | 4,8,9,10               |
|    104 | 11,12,13               |
|    105 | 19,20,21               |
+——–+————————+
5 rows in set (0.00 sec)
转自 http://blog.sina.com.cn/s/blog_4c197d420101e408.html

Check Also

mysql Incorrect datetime value

从本地 迁移数据库到测试服 发 ...

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>