SQL查询每日最大quantity及对应Category 无需JOIN的实现方法

现有测试表结构与数据如下：

Date          Category     quantity
2022-04-15    A            500
2022-04-15    B            700
2022-04-15    F            900
2022-04-16    M            400

需求为返回每日quantity最高的完整记录，包含对应Category字段，期望输出结果：

Date          Category     quantity
2022-04-15    F            900
2022-04-16    M            400

常规的GROUP BY聚合写法只能返回日期和对应的最大销量值，无法带出匹配的分类字段：

SELECT Date, max(quantity)
FROM table_name
GROUP BY Date

不需要JOIN关联即可实现需求，以下是不同场景下的可用写法：

• 窗口函数方案（通用推荐）：支持MySQL 8.0+、PostgreSQL、Hive、Spark SQL等绝大多数主流新版本数据库，逻辑清晰可读性强。通过ROW_NUMBER()给同日期下的记录按销量倒序打排名，取排名为1的记录即可：

SELECT Date, Category, quantity
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY quantity DESC) as rk
    FROM table_name
) tmp
WHERE rk = 1

注意：如果同一天存在多条销量并列第一的记录，ROW_NUMBER()只会返回其中一条；需要返回所有并列第一的记录时，把ROW_NUMBER()替换为RANK()即可。

• GROUP_CONCAT截取方案（适配老版本MySQL）：针对不支持窗口函数的MySQL 5.x环境，可以利用聚合函数按销量倒序拼接同日期下的分类值，再截取第一个分类值，全程不需要关联操作：

SELECT 
    Date,
    SUBSTRING_INDEX(GROUP_CONCAT(Category ORDER BY quantity DESC), ',', 1) as Category,
    MAX(quantity) as quantity
FROM table_name
GROUP BY Date

注意：使用前需要确认group_concat_max_len参数配置，避免分类值过长被截断。

• 元组匹配方案：支持元组条件匹配的数据库可以直接用IN子查询匹配，不需要写显式JOIN：

SELECT Date, Category, quantity
FROM table_name
WHERE (Date, quantity) IN (
    SELECT Date, MAX(quantity)
    FROM table_name
    GROUP BY Date
)

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