好文收藏-mysql分组取每组前几条记录(排序)

举报
隔壁老汪 发表于 2022/06/24 22:27:53 2022/06/24
【摘要】 mysql分组取每组前几条记录(排序)   http://blog.sina.com.cn/s/blog_4c197d420101e408.html   mysql分组取每组前几条记录(排名) 附group by与order by的研究 https://www.cnblogs.com/mo-beifeng/...

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

 

http://blog.sina.com.cn/s/blog_4c197d420101e408.html

 

mysql分组取每组前几条记录(排名) 附group by与order by的研究

https://www.cnblogs.com/mo-beifeng/archive/2012/03/26/2341886.html

 

测试数据
  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)

文章来源: blog.csdn.net，作者：隔壁老瓦，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/wxb880114/article/details/106838079

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入