如何编写SQL按指定mit_rel分组统计数量并获取最新状态？

问题描述 #

我编写了如下SQL查询：

SELECT mit_rel, status, updatedate
FROM carts
WHERE mit_rel::integer in (60855, 60763, 60607)

查询结果如下：

我需要获取每个mit_rel的count(mit_rel)（命名为countr），以及按updatedate排序的最新status和对应的updatedate，期望得到如下结果：

解决方案 #

可以使用窗口函数实现需求，具体SQL如下：

WITH cart_summary AS (
    SELECT 
        mit_rel,
        status,
        updatedate,
        COUNT(mit_rel) OVER (PARTITION BY mit_rel) AS countr,
        ROW_NUMBER() OVER (PARTITION BY mit_rel ORDER BY updatedate DESC) AS rn
    FROM carts
    WHERE mit_rel::integer IN (60855, 60763, 60607)
)
SELECT mit_rel, status, updatedate, countr
FROM cart_summary
WHERE rn = 1
ORDER BY mit_rel;

逻辑说明 #

1. 统计分组总数：通过COUNT(mit_rel) OVER (PARTITION BY mit_rel)，在每个mit_rel分组内计算记录条数，得到countr字段。
2. 标记最新记录：用ROW_NUMBER() OVER (PARTITION BY mit_rel ORDER BY updatedate DESC)，按mit_rel分组后将记录按更新时间倒序排列，最新的记录会被标记为rn=1。
3. 筛选目标数据：外层查询只保留rn=1的记录，即可得到每个分组的最新状态、对应时间以及总数。

该写法适配大多数主流数据库（如PostgreSQL、MySQL 8.0+、SQL Server等）。

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