Codeigniter+MySQL学生考勤录入的SQL查询修正求助

问题根源 #

你当前的左连接查询未限定考勤日期，只要学生有过任意历史考勤记录都会被关联返回，会出现重复行、带出旧考勤状态的问题，和你当日录入考勤的需求不符。

解决方案 #

无需调整表结构或交互设计，仅调整SQL查询即可，可选增加联合索引优化写入逻辑：

1. 调整数据查询SQL

将考勤日期过滤条件加在LEFT JOIN的ON子句中，不要加在WHERE子句中（加在WHERE会过滤掉当日未录入考勤的学生，左连接会变成类似内连接的效果），示例如下：

SELECT 
    a.s_id AS sid,
    a.s_prog_id AS progid,
    a.s_acssn_id AS ssnid,
    a.s_name AS sname,
    a.s_admission_no AS sadmsnno,
    a.s_photo_name AS sphotonm,
    IFNULL(b.sa_atten_code, '') AS acode 
FROM student_mst a 
LEFT JOIN stu_attendance b 
ON a.s_id = b.sa_s_id AND b.sa_atten_dt = '你要查询的考勤日期，格式如2024-05-20'

这里用IFNULL处理未录入考勤的学生，将NULL转为空字符串，方便前端显示未标记状态。

2. CodeIgniter中实际写法示例

// 接收前端传入的考勤日期，默认取当前服务器日期
$target_date = $this->input->get('atten_date') ?? date('Y-m-d');

// 查询构造器写法
$this->db->select('a.s_id AS sid, a.s_prog_id AS progid, a.s_acssn_id AS ssnid, a.s_name AS sname, a.s_admission_no AS sadmsnno, a.s_photo_name AS sphotonm, IFNULL(b.sa_atten_code, "") AS acode');
$this->db->from('student_mst a');
$this->db->join('stu_attendance b', "a.s_id = b.sa_s_id AND b.sa_atten_dt = '{$target_date}'", 'left');
$attendance_list = $this->db->get()->result_array();

可选优化：增加唯一索引避免重复录入

给stu_attendance表加联合唯一索引，避免同一个学生同一天被插入多条考勤记录，同时支持直接用ON DUPLICATE KEY UPDATE实现考勤新增/修改复用一套逻辑：

-- 加联合唯一索引
ALTER TABLE stu_attendance ADD UNIQUE KEY `idx_stu_date` (`sa_s_id`, `sa_atten_dt`);

AJAX提交考勤的SQL示例：

INSERT INTO stu_attendance (sa_s_id, sa_s_acssn_id, sa_s_prog_id, sa_atten_dt, sa_atten_code, sa_teacher_id)
VALUES (?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE sa_atten_code = VALUES(sa_atten_code), sa_teacher_id = VALUES(sa_teacher_id)

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