mysql根据属性分组找最值
初始化测试数据
我们有如下一张 product 订单表,相关字段分别为产品名称,订单数量,订单时间。
DDL:
-- ---------------------------- -- Table structure for product -- ---------------------------- DROP TABLE IF EXISTS `product`; CREATE TABLE `product` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `productName` varchar(255) DEFAULT NULL COMMENT '产品名称', `number` int(11) DEFAULT NULL COMMENT '订单数量', `orderTime` datetime DEFAULT NULL COMMENT '订单时间', PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 10 DEFAULT CHARSET = utf8mb4 COMMENT ='订单表'; -- ---------------------------- -- Records of product -- ---------------------------- INSERT INTO `product` VALUES ('1', '9900K', '100', '2020-08-12 16:41:55'); INSERT INTO `product` VALUES ('2', '10900K', '50', '2020-08-18 16:41:58'); INSERT INTO `product` VALUES ('3', '3950X', '10', '2020-08-10 16:42:02'); INSERT INTO `product` VALUES ('4', '3900X', '75', '2020-08-25 16:45:52'); INSERT INTO `product` VALUES ('5', '9900K', '10', '2020-07-20 16:46:30'); INSERT INTO `product` VALUES ('6', '3950X', '1', '2020-07-21 16:46:34'); INSERT INTO `product` VALUES ('7', '10900K', '5', '2020-07-05 16:46:38'); INSERT INTO `product` VALUES ('8', '9900K', '15', '2020-08-19 22:35:20'); INSERT INTO `product` VALUES ('9', '3950X', '30', '2020-06-19 22:35:51');
测试
需求:根据产品名称分组,找到订单时间最新一条记录
正确的结果集应该如下:
方法一:
select a.* from product a where a.orderTime = (select max(orderTime) from product b where b.productName = a.productName);
方法二:
这里 not exists 意思是,找出a表中不满足子查询的记录,在b表中找不到比a表的 orderTime 更大的值,也就是a的值应该是最大的。
(关于 exist 和 not exists 的用法可以这样理解,exist 后面的子查询只返回 Boolean 值,将a表中的数据与子查询逐条对比,如果子查询包含该行,那么就会返回 true 存在于结果集中)
select a.* from product a where not exists(select 1 from product b where b.productName = a.productName and b.orderTime > a.orderTime);
方法三:
select a.* from product a where exists(select count(*) from product b where b.productName = a.productName and b.orderTime > a.orderTime having count(*) = 0);
方法四:
select a.* from product a where 1 > (select count(*) from product where productName = a.productName and orderTime > a.orderTime) order by a.productName;
方法五:
select a.* from product a inner join (select productName, max(orderTime) orderTime from product group by productName) b on a.productName = b.productName and a.orderTime = b.orderTime order by a.productName;
需求:根据产品名称分组,找到表中第一次出现的记录
select a.* from product a where orderTime = (select orderTime from product where productName = a.productName limit 1) order by a.productName;
结果:
需求:根据产品名称分组,找到表中订单为最新两次的记录
方法一:
select a.* from product a where 2 > (select count(*) from product where productName = a.productName and orderTime > a.orderTime) order by a.productName, a.orderTime;
方法二:
select a.* from product a where exists(select count(*) from product where productName = a.productName and orderTime > a.orderTime having Count(*) < 2) order by a.productName;
结果:
本博客文章均已测试验证,欢迎评论、交流、点赞。
部分文章来源于网络,如有侵权请联系删除。
转载请注明原文链接:https://www.cnblogs.com/sueyyyy/p/13532411.html