MySQL关联子表最新记录并统计read_reciept计数的技术需求

我需要在MySQL中实现表关联，仅从Table_two获取每个ID的最新记录，同时统计该ID对应的read_reciept列值为1的总数量，之前找到的方案无法解决我的问题。

数据示例 #

Table_One #

Table_two #

期望结果 #

解决方案 #

适用于MySQL 8.0+（支持窗口函数） #

通过窗口函数获取每个ID的最新记录，同时子查询统计read_reciept为1的数量，最后关联表得到结果：

SELECT 
    t1.ID,
    t1.Name,
    t2_latest.`Visit ID`,
    t2_latest.Date,
    t2_stats.read_count AS read_reciept
FROM Table_One t1
JOIN (
    -- 筛选每个ID的最新记录
    SELECT 
        ID,
        `Visit ID`,
        Date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY STR_TO_DATE(Date, '%m/%d/%Y') DESC) AS rn
    FROM Table_two
) t2_latest ON t1.ID = t2_latest.ID AND t2_latest.rn = 1
JOIN (
    -- 统计每个ID的read_reciept=1的总数
    SELECT 
        ID,
        COUNT(*) AS read_count
    FROM Table_two
    WHERE read_reciept = 1
    GROUP BY ID
) t2_stats ON t1.ID = t2_stats.ID;

适用于MySQL 8.0以下版本（无窗口函数） #

用关联子查询获取最新记录，搭配统计子查询实现需求：

SELECT 
    t1.ID,
    t1.Name,
    t2.`Visit ID`,
    t2.Date,
    t2_stats.read_count AS read_reciept
FROM Table_One t1
JOIN Table_two t2 ON t1.ID = t2.ID
JOIN (
    SELECT 
        ID,
        COUNT(*) AS read_count
    FROM Table_two
    WHERE read_reciept = 1
    GROUP BY ID
) t2_stats ON t1.ID = t2_stats.ID
WHERE t2.Date = (
    SELECT MAX(STR_TO_DATE(Date, '%m/%d/%Y'))
    FROM Table_two
    WHERE ID = t2.ID
);

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