mysql根据属性分组找最值

初始化测试数据

我们有如下一张 product 订单表，相关字段分别为产品名称，订单数量，订单时间。

 DDL：

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

-- ---------------------------- -- 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');

测试

需求：根据产品名称分组，找到订单时间最新一条记录

正确的结果集应该如下：

方法一：

?

1 2 3

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 存在于结果集中）

?

1 2 3

select a.* from product a where not exists(select 1 from product b where b.productName = a.productName and b.orderTime > a.orderTime);

方法三：

?

1 2 3 4 5 6 7

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);

方法四：

?

1 2 3 4

select a.* from product a where 1 > (select count(*) from product where productName = a.productName and orderTime > a.orderTime) order by a.productName;

方法五：

?

1 2 3 4 5

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;

需求：根据产品名称分组，找到表中第一次出现的记录

?

1 2 3 4

select a.* from product a where orderTime = (select orderTime from product where productName = a.productName limit 1) order by a.productName;

 结果：

需求：根据产品名称分组，找到表中订单为最新两次的记录

方法一：

?

1 2 3 4

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;

方法二：

?

1 2 3 4 5 6 7

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;

结果：