如何在SQL中创建Count列并按月度总Count发放2000元奖金

1. 新增考勤Count列 #

根据Hours列值生成Count列，有两种实用实现方式：

方式一：查询时动态计算（不修改原表） #

适合仅在查询结果中展示Count值，无需调整表结构：

SELECT 
    *,
    CASE WHEN Hours >= 6 THEN 1 ELSE 0 END AS `Count`
FROM attendance_table;

方式二：永久新增列并赋值 #

如果需要将Count列持久化到表中：

-- 新增Count列
ALTER TABLE attendance_table ADD COLUMN `Count` INT;

-- 批量更新列值
UPDATE attendance_table
SET `Count` = CASE WHEN Hours >= 6 THEN 1 ELSE 0 END;

2. 月度奖金核算 #

先按员工和月份统计Totalcount，再筛选出符合条件的员工生成奖金记录：

-- 生成仅含符合奖金条件的核算结果
SELECT 
    employee_id,
    month,
    Totalcount,
    2000 AS bonus_amount
FROM (
    -- 统计每个员工每月的有效考勤天数（Count=1的总和）
    SELECT 
        employee_id,
        DATE_FORMAT(attendance_date, '%Y-%m') AS month,
        SUM(`Count`) AS Totalcount
    FROM attendance_table
    GROUP BY employee_id, DATE_FORMAT(attendance_date, '%Y-%m')
) AS monthly_summary
WHERE Totalcount >= 3;

如果需要包含所有员工的月度记录（即使无奖金），可调整为：

SELECT 
    employee_id,
    month,
    Totalcount,
    CASE WHEN Totalcount >= 3 THEN 2000 ELSE 0 END AS bonus_amount
FROM (
    SELECT 
        employee_id,
        DATE_FORMAT(attendance_date, '%Y-%m') AS month,
        SUM(`Count`) AS Totalcount
    FROM attendance_table
    GROUP BY employee_id, DATE_FORMAT(attendance_date, '%Y-%m')
) AS monthly_summary;

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