MySQL 5.7如何按company_id分组获取最新一条有效数据

问题背景 #

需要从subscription表中，为指定的company_id（16867、19164）各选取一条createdAt最新的记录，筛选条件为disabledAt IS NULL。

原查询及结果 #

执行以下SQL获取所有符合条件的记录：

SELECT `company_id`, `product_id`, `expiresAt`, `createdAt`, `createdBy`, `disabledAt`
FROM subscription
WHERE `disabledAt` IS NULL AND `company_id` IN(16867, 19164)
ORDER BY `createdAt` DESC;

返回结果：

company_id  product_id  expiresAt   createdAt       createdBy   disabledAt  
19164   3   2023-05-02  2022-06-09 12:41:37 1   NULL    
19164   3   2022-05-02  2022-06-09 12:40:47 1   NULL    
16867   3   2023-05-31  2022-05-20 08:18:36 1   NULL    
16867   3   2023-05-31  2022-05-20 08:18:08 1   NULL    
16867   3   2022-05-31  2022-05-12 14:49:51 1   NULL    
16867   3   2022-05-26  2021-05-26 07:20:52 1   NULL

期望结果 #

每个company_id仅保留createdAt最新的一条记录：

19164   3   2023-05-02  2022-06-09 12:41:37 1    NULL
16867   3   2023-05-31  2022-05-20 08:18:36 1    NULL

错误的GROUP BY尝试 #

直接使用GROUP BY会返回错误结果，因为MySQL 5.7开启ONLY_FULL_GROUP_BY模式时，SELECT中的非聚合字段必须包含在GROUP BY中，否则会随机返回分组内的某条记录：

SELECT `company_id`, `product_id`, `expiresAt`, `createdAt`, `createdBy`, `disabledAt`
FROM subscription
WHERE `disabledAt` IS NULL AND `company_id` IN(16867, 19164)
GROUP BY `company_id`
ORDER BY `createdAt` DESC;

错误结果：

company_id  product_id  expiresAt   createdAt       createdBy   disabledAt  
19164   3   2022-05-02  2022-06-09 12:40:47 1   NULL    
16867   3   2022-05-26  2021-05-26 07:20:52 1   NULL    

正确实现方案 #

由于MySQL 5.7不支持窗口函数，可通过以下两种方式实现需求：

方案一：子查询+关联查询 #

先通过子查询获取每个company_id对应的最新createdAt，再关联原表筛选匹配记录：

SELECT s.`company_id`, s.`product_id`, s.`expiresAt`, s.`createdAt`, s.`createdBy`, s.`disabledAt`
FROM subscription s
INNER JOIN (
    -- 子查询：获取符合条件的每个company_id的最新createdAt
    SELECT `company_id`, MAX(`createdAt`) AS max_createdAt
    FROM subscription
    WHERE `disabledAt` IS NULL AND `company_id` IN(16867, 19164)
    GROUP BY `company_id`
) t ON s.`company_id` = t.`company_id` AND s.`createdAt` = t.max_createdAt
WHERE s.`disabledAt` IS NULL;

方案二：关联子查询筛选 #

对每条记录，判断其createdAt是否为对应company_id下的最大值：

SELECT `company_id`, `product_id`, `expiresAt`, `createdAt`, `createdBy`, `disabledAt`
FROM subscription s
WHERE `disabledAt` IS NULL 
  AND `company_id` IN(16867, 19164)
  AND `createdAt` = (
      -- 子查询：获取当前company_id下符合条件的最大createdAt
      SELECT MAX(`createdAt`)
      FROM subscription
      WHERE `company_id` = s.`company_id` AND `disabledAt` IS NULL
  );

内容的提问来源于stack exchange，提问作者talhaamir