如何从登录表按月获取每日最新登录记录并生成按日排列的出勤数据

你可以通过提取日期维度+行转列聚合的方式实现需求，以下是具体实现方案： #

前提说明 #

原始打卡记录表结构如下：

表中Date_Time为打卡时间字段，Flag字段标识打卡状态：Flag=0为登录，Flag=1为登出。
期望输出的按日期排列的出勤统计格式如下：
#

通用实现逻辑 #

1. 从Date_Time字段拆分出单独的日期维度，区分登录、登出两条记录
2. 按用户ID、日期分组聚合，得到每个用户每日的登录、登出时间
3. 通过行转列语法，将日期转为列、用户作为行，对应单元格填充当日出勤状态或打卡时间段

主流数据库实现示例 #

MySQL 实现

-- 构造每日用户打卡基础宽表
WITH daily_attendance AS (
    SELECT 
        user_id,
        username,
        DATE(Date_Time) AS attendance_date,
        MAX(IF(Flag=0, Date_Time, NULL)) AS login_time,
        MAX(IF(Flag=1, Date_Time, NULL)) AS logout_time
    FROM 你的表名
    GROUP BY user_id, username, DATE(Date_Time)
)
-- 行转列得到最终结果，可按需调整日期范围
SELECT 
    user_id,
    username,
    MAX(IF(attendance_date='2024-01-01', CONCAT(login_time,'~',logout_time), '缺勤')) AS `2024-01-01`,
    MAX(IF(attendance_date='2024-01-02', CONCAT(login_time,'~',logout_time), '缺勤')) AS `2024-01-02`,
    -- 按需补充更多日期列
    MAX(IF(attendance_date='2024-01-31', CONCAT(login_time,'~',logout_time), '缺勤')) AS `2024-01-31`
FROM daily_attendance
GROUP BY user_id, username;

PostgreSQL 实现

WITH daily_attendance AS (
    SELECT 
        user_id,
        username,
        Date_Time::date AS attendance_date,
        MAX(CASE WHEN Flag=0 THEN Date_Time END) AS login_time,
        MAX(CASE WHEN Flag=1 THEN Date_Time END) AS logout_time
    FROM 你的表名
    GROUP BY user_id, username, Date_Time::date
)
SELECT * FROM crosstab(
    'SELECT user_id, username, attendance_date, CONCAT(login_time,''~'',logout_time) FROM daily_attendance ORDER BY 1,2',
    'SELECT DISTINCT attendance_date FROM daily_attendance ORDER BY 1'
) AS ct(
    user_id INT, 
    username VARCHAR, 
    "2024-01-01" VARCHAR, 
    "2024-01-02" VARCHAR, 
    -- 列名和数量需和上方子查询返回的日期完全匹配
    "2024-01-31" VARCHAR
);

优化说明 #

如果不想硬编码日期列，可以结合存储过程或者后端业务代码，先查询你需要统计的时间范围内的所有日期，再动态拼接SQL语句即可实现动态列效果。

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