CodeSnippet.Cn
代码片段
Csharp
架构设计
.NetCore
西班牙语
kubernetes
MySql
Redis
Algorithm
Ubuntu
Linux
Other
.NetMvc
VisualStudio
Git
pm
Python
WPF
java
Plug-In
分布式
CSS
微服务架构
JavaScript
DataStructure
Shared
Mysql分组取每组前几条记录
0
MySql
小笨蛋
发布于:2021年10月23日
更新于:2021年10月23日
131
#custom-toc-container
### 1.数据库表初始化 表mygoods为商品表,cat_id为分类id,goods_id为商品id,status为商品当前的状态位(1:有效,0:无效)。建表语句如下所示: ```sql 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); ``` ### 2.每个分类找出价格最高的两个商品 查询每个分类中价格最高的两个商品,SQL语句如下所示: ```sql 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; ``` 执行结果如下所示: ```sql 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 19 105 85 1 ``` ### 3.每个分类找出有效的价格最高的两个商品 查询每个分类中有效的价格最高的两个商品,SQL语句如下所示: ```sql 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; ``` 执行结果如下图所示: ```sql 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 19 105 85 1 ``` ### 4.每个分类找出有效的价格最高的两个商品 查询每个分类中有效的价格最高的两个商品,SQL语句如下所示: ```sql 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; ``` 执行结果如下图所示: ```sql 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 19 105 85 1 ``` 总结: 由上可知如果需要增加条件的话,需要在两处增加条件。
这里⇓感觉得写点什么,要不显得有点空,但还没想好写什么...
返回顶部
About
京ICP备13038605号
© 代码片段 2024