针对MySQL 5.5之前的，使用子查询嵌套出现异常，不能识别外层table表问题，而做出的having和count聚合函数配合代替limit

首先说下业务需求：一个班次中有多个学员，需要一个学员连续四次没有出勤的，将这种学员状态识别为结课学员

以下有两张表：class_schedule为班次表 student_schedule为学员排课表

班次表记录着每天的班次，课程，班级和上课时间

排课表记录着每个学员在哪个班次中有课

通过需求我们了解到，主表为student_schedule,需要联查class_schedule,取 class_schedule表中的上课时间作为排序标识，因为需求中说到连续四次没有出勤，那么从MySQL8.0之后，支持多重嵌套子查询，sql就简单易懂了：

select *
    from
        (select
        student.student_code,
        student.student_name,
        student.gender,
        student.birthday,
        student.phone,
        student.phone_holder_name,
        student.relation_with_phone_holder,
        case
        when(
        select ifnull(sum(amount),0) from period_flow
        where student_id=student.id
        )=0 and (select count(1) from student_schedule where student_id=student.id and schedule_type=2) > 0
        then 3
        when (
        select ifnull(sum(amount),0) from period_flow
        where student_id=student.id
        )=0
        then 0
        when(
        (select
        if(student_schedule.attend_status =1,1,0)a
        from class_schedule
        left join student_schedule on student_schedule.class_schedule_id=class_schedule.id
        where class_schedule.begin_time < now()
        and student_schedule.student_id=student.id
        ORDER BY class_schedule.begin_time desc limit 0,1)
        +
        (select
        if(student_schedule.attend_status =1,1,0)a
        from class_schedule
        left join student_schedule on student_schedule.class_schedule_id=class_schedule.id
        where class_schedule.begin_time < now()
        and student_schedule.student_id=student.id
        ORDER BY class_schedule.begin_time desc limit 1,1)
        +
        (select
        if(student_schedule.attend_status =1,1,0)a
        from class_schedule
        left join student_schedule on student_schedule.class_schedule_id=class_schedule.id
        where class_schedule.begin_time < now()
        and student_schedule.student_id=student.id
        ORDER BY class_schedule.begin_time desc limit 2,1)
        +
        (select
        if(student_schedule.attend_status =1,1,0)a
        from class_schedule
        left join student_schedule on student_schedule.class_schedule_id=class_schedule.id
        where class_schedule.begin_time < now()
        and student_schedule.student_id=student.id
        ORDER BY class_schedule.begin_time desc limit 3,1)
        )=4
        then 2
        else 1
        end
        student_status,
        admissions_channel.channel_name,
        user.user_name,
        student_user.user_account,

        student.id,
        student.avatar_url,
        student.admissions_channel_id,
        student.course_seller_user_id,
        student.remarks,
        student.create_time,
        student.delete_value
        from
        student
        left join admissions_channel on admissions_channel.id=student.admissions_channel_id
        left join user on user.id=student.course_seller_user_id
        left join student_user on student_user.student_id=student.id)t
        where
        t.delete_value is null

但是我们发现这种对于版本5.5的就会报错

 

所以这种方法不可取

那就引用到今天的要说的count聚合函数了

       SELECT
                *
        FROM
            (SELECT
                student.student_code,
                student.student_name,
                student.gender,
                student.birthday,
                student.phone,
                student.phone_holder_name,
                student.relation_with_phone_holder,
                CASE
                WHEN (
                SELECT ifnull(sum(amount),0) FROM period_flow
                WHERE student_id=student.id
                )=0
                THEN 0
                WHEN
                k.num=4
                THEN 2
                ELSE 1
                END
                student_status,
                admissions_channel.channel_name,
                user.user_name,
                student_user.user_account,

                student.id,
                student.avatar_url,
                student.admissions_channel_id,
                student.course_seller_user_id,
                student.remarks,
                student.create_time,
                student.delete_value
            FROM
                student
            LEFT JOIN admissions_channel ON admissions_channel.id=student.admissions_channel_id
            LEFT JOIN user on user.id=student.course_seller_user_id
            LEFT JOIN student_user on student_user.student_id=student.id
            LEFT JOIN
                (SELECT
                    SUM(num) num,
                    student_id
                FROM
                    (SELECT
                        a.id,
                        a.student_id,
                        a.begin_time,
                        COUNT(a.begin_time),
                        a.num
                    FROM
                        (SELECT
                        student_schedule.id,
                        student_schedule.student_id,
                        class_schedule.begin_time,
                        IF(student_schedule.attend_status =1,1,0) num
                        FROM student_schedule
                        LEFT JOIN class_schedule ON class_schedule.id=student_schedule.class_schedule_id
                        WHERE class_schedule.begin_time < now()
                        AND student_schedule.schedule_type=1
                        GROUP BY student_schedule.student_id,student_schedule.class_schedule_id
                        ORDER BY student_schedule.student_id ASC,class_schedule.begin_time DESC) a
                    LEFT JOIN
                        (SELECT
                        student_schedule.id,
                        student_schedule.student_id,
                        class_schedule.begin_time,
                        IF(student_schedule.attend_status =1,1,0) num
                        FROM student_schedule
                        LEFT JOIN class_schedule ON class_schedule.id=student_schedule.class_schedule_id
                        WHERE class_schedule.begin_time < now()
                        AND student_schedule.schedule_type=1
                        GROUP BY student_schedule.student_id,student_schedule.class_schedule_id
                        ORDER BY student_schedule.student_id ASC,class_schedule.begin_time DESC) b
                    ON a.student_id=b.student_id AND a.begin_time <= b.begin_time
                    GROUP BY a.student_id,a.begin_time
                    HAVING COUNT(a.begin_time) <=4) f
                GROUP BY student_id)k on k.student_id=student.id )t
        WHERE
            t.delete_value IS NULL