基于SQL Server实现员工考勤数据转置及汇总查询

需求说明 #

将employeeAttendance表（字段：EmployeeID、companyID、process date、DayStatus）转换为按员工一行展示的格式：

• 列展示当月1-31日的考勤状态（命名为D1至D31）
• 附加统计列：Total Present（出勤总天数）、Total Holidays（假期总天数）、Total Absent（缺勤总天数）
• 支持多员工批量展示

前提假设 #

1. process date为标准日期类型字段
2. DayStatus的取值为明确枚举值（例如：Present、Holiday、Absent）
3. 仅处理当月数据（若需跨月可调整日期过滤条件）

MySQL 实现方案 #

使用条件聚合实现转置，无需依赖特定PIVOT语法：

SELECT
    EmployeeID,
    companyID,
    -- 生成D1-D31的每日状态列
    MAX(CASE WHEN DAY(process_date) = 1 THEN DayStatus END) AS D1,
    MAX(CASE WHEN DAY(process_date) = 2 THEN DayStatus END) AS D2,
    MAX(CASE WHEN DAY(process_date) = 3 THEN DayStatus END) AS D3,
    -- 省略D4至D30的重复代码，按需补充
    MAX(CASE WHEN DAY(process_date) = 31 THEN DayStatus END) AS D31,
    -- 统计各状态总天数
    SUM(CASE WHEN DayStatus = 'Present' THEN 1 ELSE 0 END) AS `Total Present`,
    SUM(CASE WHEN DayStatus = 'Holiday' THEN 1 ELSE 0 END) AS `Total Holidays`,
    SUM(CASE WHEN DayStatus = 'Absent' THEN 1 ELSE 0 END) AS `Total Absent`
FROM
    employeeAttendance
-- 可选：过滤指定年月的数据，例如2024年5月
WHERE
    YEAR(process_date) = 2024 AND MONTH(process_date) = 5
GROUP BY
    EmployeeID, companyID;

代码说明 #

• DAY(process_date)提取日期的日部分，匹配1-31
• MAX(CASE ...)确保每个员工每日仅返回一个状态（若单日有多条数据需先去重）
• SUM(CASE ...)按状态统计总天数
• GROUP BY按员工和公司分组，保证一行一个员工

SQL Server 实现方案 #

使用原生PIVOT语法简化转置逻辑：

WITH DailyAttendance AS (
    SELECT
        EmployeeID,
        companyID,
        'D' + CAST(DAY(process_date) AS VARCHAR(2)) AS DayCol,
        DayStatus,
        -- 预统计各状态计数
        CASE WHEN DayStatus = 'Present' THEN 1 ELSE 0 END AS PresentCnt,
        CASE WHEN DayStatus = 'Holiday' THEN 1 ELSE 0 END AS HolidayCnt,
        CASE WHEN DayStatus = 'Absent' THEN 1 ELSE 0 END AS AbsentCnt
    FROM
        employeeAttendance
    WHERE
        YEAR(process_date) = 2024 AND MONTH(process_date) = 5
)
SELECT
    EmployeeID,
    companyID,
    -- 转置后的每日状态列
    D1, D2, D3, /* ... 补充D4至D30 ... */ D31,
    -- 汇总统计值
    SUM(PresentCnt) AS [Total Present],
    SUM(HolidayCnt) AS [Total Holidays],
    SUM(AbsentCnt) AS [Total Absent]
FROM
    DailyAttendance
PIVOT (
    MAX(DayStatus)
    FOR DayCol IN (D1, D2, D3, /* ... 补充D4至D30 ... */ D31)
) AS PivotTable
GROUP BY
    EmployeeID, companyID, D1, D2, D3, /* ... 补充D4至D30 ... */ D31;

代码说明 #

• 先通过CTE生成带DayCol（D1-D31）的中间表
• PIVOT函数直接将行转列为每日状态列
• 最后通过SUM汇总各状态总天数

注意事项 #

1. 若存在单日多条考勤记录，需先通过DISTINCT或GROUP BY EmployeeID, process_date去重
2. 若需动态生成D1-D31列（避免硬编码），可使用动态SQL实现
3. 跨年月统计时，需在GROUP BY中加入YEAR(process_date)和MONTH(process_date)

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