MySQL按最新/最旧日期统计用户记录数并插入新表的实现

你遇到的问题是：按email分组统计考勤记录总数，同时要取该用户最新/最旧tDate对应的col4、col5等字段，普通GROUP BY做不到——因为GROUP BY只能做聚合计算，没法直接关联到对应日期的行数据。下面给两种靠谱的解决办法：

方案一：窗口函数（优先用，支持主流新数据库） #

用ROW_NUMBER()窗口函数给每个用户的记录按日期排序编号，然后只保留每组的第一条，同时统计总记录数：

INSERT INTO newTable (email, record_count, col4, col5, ...)
SELECT 
    email,
    COUNT(*) OVER (PARTITION BY email) AS record_count,
    col4,
    col5,
    -- 其他需要保留的字段
FROM (
    SELECT 
        *,
        -- DESC=取最新记录，ASC=取最旧记录
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY tDate DESC) AS row_num
    FROM tempTable
) filtered
WHERE row_num = 1;

逻辑说明：内层查询给每个用户的每条记录按日期排好序并标上序号，外层只拿每组的第一条；同时用COUNT(*) OVER()直接算出该用户的总记录数，一步到位满足需求。

方案二：子查询关联（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），就用子查询先拿到每个用户的最新/最旧日期和总记录数，再关联原表取对应字段：

INSERT INTO newTable (email, record_count, col4, col5, ...)
SELECT 
    t.email,
    user_stats.record_count,
    t.col4,
    t.col5,
    -- 其他需要保留的字段
FROM tempTable t
INNER JOIN (
    SELECT 
        email,
        COUNT(*) AS record_count,
        MAX(tDate) AS target_date -- MAX=最新，MIN=最旧
    FROM tempTable
    GROUP BY email
) user_stats 
ON t.email = user_stats.email AND t.tDate = user_stats.target_date;

注意事项：如果同一个用户同一天有多条记录，这个SQL会返回多条结果；要是想只留一条，可以加DISTINCT，或者在子查询里再加额外条件（比如取最大的col4值）。

小提示 #

• 切换最新/最旧记录只需要改排序方向（方案一）或者把MAX换成MIN（方案二），非常灵活。
• 确保email是用户的唯一标识，不然分组逻辑会出错。

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