SQL按ID分组仅统计每组最新日期下各Team记录数的查询方法

我在SQL中有如下数据：

需要按ID字段分组，仅针对每个ID对应的最新date record日期下的记录，统计各Team对应的条目数量，期望输出结果如下：

注意：你的date record字段存储的是dd/MM/yyyy格式的字符串，直接做大小比较会得到错误排序结果，必须先转成日期类型再计算最大值。

写法1：窗口函数（支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等新版本数据库） #

用RANK()窗口函数给每个ID下的记录按转换后的日期倒序排名，筛选出排名为1（即最新日期）的记录后，再按维度分组计数即可，这种写法逻辑清晰性能更好：

WITH ranked_records AS (
    SELECT
        ID,
        `date record`,
        Team,
        RANK() OVER (
            PARTITION BY ID 
            ORDER BY STR_TO_DATE(`date record`, '%d/%m/%Y') DESC
        ) AS rk
    FROM your_table -- 替换为实际表名
)
SELECT
    ID,
    `date record`,
    Team,
    COUNT(*) AS `Count`
FROM ranked_records
WHERE rk = 1
GROUP BY ID, `date record`, Team;

不同数据库日期转换函数替换说明：PostgreSQL/Oracle用TO_DATE("date record", 'DD/MM/YYYY')，SQL Server用CONVERT(DATE, [date record], 103)。

写法2：关联子查询（兼容MySQL 5.x等不支持窗口函数的旧版本数据库） #

先通过子查询查出每个ID对应的最新日期，再关联原表过滤出匹配最新日期的记录，最后分组统计：

SELECT
    t.ID,
    t.`date record`,
    t.Team,
    COUNT(*) AS `Count`
FROM your_table t
INNER JOIN (
    SELECT
        ID,
        MAX(STR_TO_DATE(`date record`, '%d/%m/%Y')) AS max_date
    FROM your_table
    GROUP BY ID
) m ON t.ID = m.ID 
    AND STR_TO_DATE(t.`date record`, '%d/%m/%Y') = m.max_date
GROUP BY t.ID, t.`date record`, t.Team;

两种写法执行后都能得到你期望的输出结果。

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