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

时间：2021-08-29 15:13:28
[sql]  view plain copy
  1. CREATE TABLE `mygoods` (  
  2.   `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  3.   `cat_id` int(11) NOT NULL DEFAULT '0',  
  4.   `price` tinyint(3) NOT NULL DEFAULT '0',  
  5.   `status` tinyint(3) DEFAULT '1',  
  6.   PRIMARY KEY (`goods_id`),  
  7.   KEY `icatid` (`cat_id`)  
  8. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;  
  9.   
  10. INSERT INTO `mygoods` VALUES (1, 101, 90, 0);  
  11. INSERT INTO `mygoods` VALUES (2, 101, 99, 1);  
  12. INSERT INTO `mygoods` VALUES (3, 102, 98, 0);  
  13. INSERT INTO `mygoods` VALUES (4, 103, 96, 0);  
  14. INSERT INTO `mygoods` VALUES (5, 102, 95, 0);  
  15. INSERT INTO `mygoods` VALUES (6, 102, 94, 1);  
  16. INSERT INTO `mygoods` VALUES (7, 102, 93, 1);  
  17. INSERT INTO `mygoods` VALUES (8, 103, 99, 1);  
  18. INSERT INTO `mygoods` VALUES (9, 103, 98, 1);  
  19. INSERT INTO `mygoods` VALUES (10, 103, 97, 1);  
  20. INSERT INTO `mygoods` VALUES (11, 104, 96, 1);  
  21. INSERT INTO `mygoods` VALUES (12, 104, 95, 1);  
  22. INSERT INTO `mygoods` VALUES (13, 104, 94, 1);  
  23. INSERT INTO `mygoods` VALUES (15, 101, 92, 1);  
  24. INSERT INTO `mygoods` VALUES (16, 101, 93, 1);  
  25. INSERT INTO `mygoods` VALUES (17, 101, 94, 0);  
  26. INSERT INTO `mygoods` VALUES (18, 102, 99, 1);  
  27. INSERT INTO `mygoods` VALUES (19, 105, 85, 1);  
  28. INSERT INTO `mygoods` VALUES (20, 105, 89, 0);  
  29. 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)
标签：

相关文章