SQL如何根据用户名和日期获取最新id并统计用户出现次数

假设你的表名为user_records，可以用以下方法实现需求：

方案1：窗口函数写法（支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库） #

SELECT id, user, count
FROM (
    SELECT 
        id,
        user,
        COUNT(*) OVER (PARTITION BY user) AS count,
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY date DESC) AS rn
    FROM user_records
) temp
WHERE rn = 1;

逻辑说明： #

• 用COUNT(*) OVER (PARTITION BY user)窗口函数直接统计每个用户的总出现次数，同用户的所有行该值一致
• 用ROW_NUMBER() OVER (PARTITION BY user ORDER BY date DESC)给同用户的行按日期倒序排序，最新日期的行序号为1
• 外层过滤序号为1的行，即可得到每个用户最新日期对应的id和总次数

方案2：低版本MySQL兼容写法（不支持窗口函数的场景） #

SELECT 
    r.id,
    r.user,
    c.count
FROM user_records r
-- 关联总次数字段
INNER JOIN (
    SELECT user, COUNT(*) AS count 
    FROM user_records 
    GROUP BY user
) c ON r.user = c.user
-- 关联每个用户的最大日期
INNER JOIN (
    SELECT user, MAX(date) AS max_date 
    FROM user_records 
    GROUP BY user
) m ON r.user = m.user AND r.date = m.max_date;

注意事项 #

如果同一个用户同一天存在多条记录，方案1的ROW_NUMBER会默认返回其中一条，若需要固定返回最大/最小id，可以修改排序规则为ORDER BY date DESC, id DESC/ORDER BY date DESC, id ASC；方案2会返回该用户当天所有的记录id，可根据实际需求调整。

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