时间戳

首页 > 解决方案 > MySQL Select with MAX() 和 GROUP BY 返回正确的最大结果但不正确的 id

mysql - MySQL Select with MAX() 和 GROUP BY 返回正确的最大结果但不正确的 id

问题描述

这是我的桌子

CREATE TABLE `capacity_log` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`date_occurred` DATETIME NOT NULL,
`ip_address` VARCHAR(255) NOT NULL DEFAULT '',
`order_product_id` INT UNSIGNED NOT NULL,
`serial` VARCHAR(255) NOT NULL DEFAULT '',
`used_capacity` BIGINT NULL DEFAULT NULL,
`aux2` INT NULL DEFAULT NULL,
`request` BLOB NULL,
`retry_count` INT NOT NULL DEFAULT '0',
`fetch_time` INT NOT NULL DEFAULT '0',
`response` BLOB NULL,
`custom_fetch_time` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`id`))

通过执行查询，我想获取每个 order_product_id 每周的行 ID 和最大使用容量。请求返回正确的最大容量，但没有返回正确的 id。

SELECT c.id, MAX(c.used_capacity) AS `max_capacity`
FROM capacity_log c
WHERE c.date_occurred < '2020-10-1' AND c.aux2 IS NULL 
GROUP BY
    YEAR(c.date_occurred), WEEK(c.date_occurred),
    c.order_product_id

标签： mysql

解决方案

您可以使用

SELECT c.id, c.used_capacity
FROM capacity_log c
WHERE  max_capacity = (
  SELECT  MAX(c.used_capacity) AS `max_capacity`
    FROM capacity_log c
   WHERE c.date_occurred < '2020-10-1' 
     AND c.aux2 IS NULL 
   GROUP 
      BY YEAR(c.date_occurred)
       , WEEK(c.date_occurred)
       , c.order_product_id)

推荐阅读