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;

结果:

 

posted @ 2020-08-19 22:47  少说点话  阅读(132)  评论(1编辑  收藏
网站运行: