编写教师月度考勤导出Excel功能所需SQL查询求助

你现有的两张表完全可以满足需求。

前置表结构假设（根据你的截图推导） #

• 教师表（假设表名：teacher）：核心字段为教师ID（t_id）、教师姓名（t_name）
• 教师考勤表（假设表名：teacher_attendance）：核心字段为关联教师ID（ta_teacher_id）、考勤日期（ta_date）、上班打卡时间（ta_clock_in_tm）、下班打卡时间（ta_clock_out_tm）

实现思路 #

1. 生成指定年月的所有日期序列，和所有教师做笛卡尔积，确保每个教师对应当月所有日期
2. 左关联考勤表，按规则判断每日考勤状态：
   • 当天为周日 → 标记S
   • 非周日，考勤表有对应记录且上下班打卡时间均不为空 → 标记P
   • 其余情况 → 标记A
3. 对结果按教师分组行转列得到每日考勤状态，同时统计总出勤、缺勤天数

适配你的代码的SQL生成逻辑（以MySQL为例） #

你需要动态拼接SQL的行转列部分，和你前面拼接表头的逻辑对应，替换你代码里$sql=""的部分即可：

// 拼接每日状态、统计字段
$dayFields = [];
$totalPresentCase = [];
$totalAbsentCase = [];
$weekendCount = 0;
for($i=1; $i<=$noDaysInGvnMonth; $i++){
    $currDate = $currYr."-".$mnth."-".$i;
    $weekday = date("w", strtotime($currDate)); // 0代表周日
    if($weekday == 0){
        $weekendCount++;
        $dayFields[] = "'S' as `d$i`";
    }else{
        $dayFields[] = "CASE 
            WHEN ta.ta_clock_in_tm IS NOT NULL AND ta.ta_clock_out_tm IS NOT NULL THEN 'P'
            ELSE 'A'
        END as `d$i`";
        $totalPresentCase[] = "CASE WHEN ta.ta_clock_in_tm IS NOT NULL AND ta.ta_clock_out_tm IS NOT NULL THEN 1 ELSE 0 END";
        $totalAbsentCase[] = "CASE WHEN ta.ta_clock_in_tm IS NULL OR ta.ta_clock_out_tm IS NULL THEN 1 ELSE 0 END";
    }
}
$dayFieldsStr = implode(",", $dayFields);
$totalPresentStr = implode("+", $totalPresentCase);
$totalAbsentStr = implode("+", $totalAbsentCase);
$totalWorkingDays = $noDaysInGvnMonth - $weekendCount;
$startDate = $currYr."-".$mnth."-01";
$endDate = $currYr."-".$mnth."-".$noDaysInGvnMonth;

// 最终SQL
$sql = "SELECT 
    @row := @row + 1 as `Sl No`,
    t.t_name as `Emp Name`,
    $dayFieldsStr,
    $totalWorkingDays as `Total Working Days`,
    $totalAbsentStr as `Total Absent`,
    $totalPresentStr as `Total Present`,
    '' as `Remarks`
FROM teacher t
CROSS JOIN (SELECT @row := 0) as rn
LEFT JOIN teacher_attendance ta ON t.t_id = ta.ta_teacher_id AND ta.ta_date BETWEEN '$startDate' AND '$endDate'
GROUP BY t.t_id, t.t_name
ORDER BY t.t_id";

注意把上述代码里的表名、字段名替换成你实际的表字段即可。

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