学生归寝日报sql

SELECT ds.constant_date,
ds.stu_name,
concat(ds.d_name, '栋/', ds.room_no, '房/', ds.bed_no, '号床') AS dorm,
ds.stu_no,
ds.stu_class_name,
<!--配合group by查询每个学生最新的识别记录时间-->
max(fir.identify_time) as idTime,
fir.direction,
fir.identify_record,
<!--1正常归寝,2未出门,3晚归,4未归-->
CASE
WHEN date_format(max(fir.identify_time), '%T:%f') &lt; ft.return_late
AND fir.direction = 1 THEN
1
WHEN ds.attendance = 1
AND fir.identify_record IS NULL
THEN
2
WHEN ft.return_late &lt; date_format(max(fir.identify_time), '%T:%f')
AND date_format(max(fir.identify_time), '%T:%f') &lt; ft.return_end AND fir.direction = 1 THEN 3
WHEN (date_format(max(fir.identify_time), '%T:%f') > ft.return_end) and fir.direction = 0
OR (ds.attendance = 0 AND fir.identify_record IS NULL) THEN
4
ELSE 0
END AS returnFlag
FROM (
<!--为每个学生生成连续的时间段,笛卡尔乘积(交叉连接)-->
SELECT s.stu_no,
s.stu_id,
constant_date,
s.stu_class_name,
s.stu_name,
s.d_name,
s.room_no,
s.bed_no,
s.attendance,
s.stu_leave
FROM face_student s,
(
<!--生成连续日期操作-->
SELECT DATE_FORMAT(ADDDATE(#{beginTime}, INTERVAL @dateAdd := @dateAdd + 1 DAY),
'%Y-%m-%d') constant_date
<!--生成的日期多少取决于该表,记录数少而时间跨度大则生成的日期不全-->
FROM sys_user
LEFT JOIN (SELECT @dateAdd := - 1) t ON 1 = 1
WHERE DATE_FORMAT(ADDDATE(#{beginTime}, INTERVAL @dateAdd DAY), '%Y-%m-%d') &lt;
DATE_FORMAT(#{endTime}, '%Y-%m-%d')) d
) ds
LEFT JOIN face_identify_record fir ON ds.constant_date = DATE_FORMAT(fir.identify_time, '%Y-%m-%d')
AND ds.stu_no = fir.stu_no
LEFT JOIN face_timing ft ON fir.return_id = ft.timing_id
left join face_bed b on ds.stu_no = b.stu_no
<if test="classIds != null">
    left join face_classes c on ds.stu_no = b.stu_no
</if>
<if test="userId != null">
    left join face_dorm_manager m on m.dormitory_id = fir.dormitory_id
    left join sys_user u on m.user_id = u.user_id
</if>
<where>
    <if test="classIds != null and classIds.size()>0">
        and c.class_id in
        <foreach item="item" collection="classIds" open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
    <if test="roomIds != null and roomIds.size()>0">
        and b.room_id in
        <foreach item="item" collection="roomIds" open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
    <if test="userId != null">and u.user_id = #{userId}</if>
    <if test="nameOrNo != null and nameOrNo != ''">and stu_name like concat('%',#{nameOrNo},'%') or ds.stu_no like concat('%',#{nameOrNo},'%')</if>
    and ds.stu_leave = 0
    <!--查询的学生的床位不应为空-->
    and b.bed_id is not null
</where>
GROUP BY ds.constant_date,
ds.stu_id

结果