能否在GROUP BY查询中指定返回每组最新issue_date的行？

嘿，这个需求我太熟悉了！咱们先来理清楚核心问题：你需要先通过issue_date <= ?2过滤数据，然后按shelf_id分组，每组只留下issue_date最新的那一行，而且不想用子查询对吧？

先说说原来GROUP BY的问题 #

你之前加GROUP BY shelf_id后得到的结果不对，是因为当你GROUP BY某列时，如果SELECT里包含非聚合的字段（比如issue_date、current_qty），数据库只会返回每组里的任意一行（具体哪行取决于数据库的内部逻辑，比如你这里就返回了shelf_id=10的最早行），而不是你想要的最新行。用HAVING确实没法直接把issue_date和它对应的current_qty绑定到MAX(issue_date)上，这也是GROUP BY的局限性。

无需子查询的完美方案：用QUALIFY子句 #

如果你的数据库支持QUALIFY（比如BigQuery、Snowflake、PostgreSQL 13+、Oracle 12c+等），那这就是最直接的无子女查询写法：

SELECT shelf_id, issue_date, current_qty
FROM Stock 
WHERE barcode = '555' AND issue_date <= '2018-05-30 14:28:32'
QUALIFY ROW_NUMBER() OVER (PARTITION BY shelf_id ORDER BY issue_date DESC) = 1;

解释下这个写法：

• ROW_NUMBER() OVER (PARTITION BY shelf_id ORDER BY issue_date DESC)：给每个shelf_id分组里的行按issue_date倒序编号，最新的行编号为1。
• QUALIFY：专门用来筛选窗口函数结果的子句，直接把编号为1的行留下来，完全不需要嵌套子查询。
• 你的issue_date <= ?2过滤条件保留在WHERE里，先完成初步数据过滤，再处理分组取最新，完全符合你的需求。

如果数据库不支持QUALIFY怎么办？ #

要是你的数据库（比如MySQL 8.0之前的版本）不支持QUALIFY，那可能没办法完全避免子查询，但可以用窗口函数的嵌套写法（这其实是很多人常用的方案，逻辑清晰）：

SELECT shelf_id, issue_date, current_qty
FROM (
    SELECT 
        shelf_id, 
        issue_date, 
        current_qty,
        ROW_NUMBER() OVER (PARTITION BY shelf_id ORDER BY issue_date DESC) AS rn
    FROM Stock 
    WHERE barcode = '555' AND issue_date <= '2018-05-30 14:28:32'
) t
WHERE rn = 1;

不过既然你明确要求无需子查询，优先推荐QUALIFY的写法，这是目前最贴合你需求的方案。

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