mysql中实现按照固定周数信息返回业务数据

1.需求描述

        1.支持按照周数查询周一到周天的课程信息,不限制查询上周下周数;

        2.前后端返参协议:

周二到周五由于页面的原因隐藏不进行显示.要求按照一周的格式进行返回,如果某天没有数据则需要返回空集合,但是天数信息需要进行返回,以便于前端遍历渲染.

2.实现方案

        1.mysql中实现根据周数查询一周的课程信息:

SELECT wy_Client_user.`first_name`,manage_course.`course_name`,t.LEVEL,t.color,DAYOFWEEK(t.start_time)-1 week_day,DATE_FORMAT(t.start_time,'%H:%m') start_time,DATE_FORMAT(t.end_time,'%H:%m') end_time
FROM 
	(SELECT LEVEL,color,start_time,end_time,teacher_id,course_id FROM manage_course_table
	WHERE YEARWEEK( DATE_FORMAT(  start_time,'%Y-%m-%d' ),1 ) = YEARWEEK(NOW(),1)+(#{weekCount}) AND STATUS=1 ) t
LEFT JOIN wy_Client_user ON t.`teacher_id`=wy_Client_user.`login`
LEFT JOIN manage_course ON manage_course.`id`=t.`course_id` WHERE manage_course.`studio_id`=1 ORDER BY t.start_time ASC

 核心业务说明:

select  * from 表 WHERE YEARWEEK( DATE_FORMAT(  时间字段,'%Y-%m-%d' ),1 ) = YEARWEEK(NOW(),1)+(#{weekCount})   

weekCount表示是要查询的周数,本周传0,下一周传1,下下周传2,以此类推;上周传-1,上上周传-2,以此类推.

        2.按照一周的天数进行返回,某天没有数据的也要返回周几数.这种场景想到的方案是类似于按照一周的天数进行左连接查询业务数据.没有数据的就是空值,保证周一到周天都有数据.可以建立一个日期天数表manage_week_day,存储周一到周天的日期.然后此表与业务查询sql进行左连接查询.使用collection标签进行组装数据返回.

manage_week_day日期表信息:

mapper接口实现:

List<StudioCourseWeekTableVo> findStudioCourseWeekTable(@Param("courseWeekTableDo")CourseWeekTableDo courseWeekTableDo);

mybatis配置文件写法:

<resultMap id="weekCourseMap" type="com.kawaxiaoyu.api.appointCourse.vo.StudioCourseWeekTableVo">
        <id property="weekId" column="week_id"></id>
        <collection property="weekCourseTables" javaType="java.util.List" ofType="com.kawaxiaoyu.api.appointCourse.vo.WeekCourseTable">
            <result property="firstName" column="first_name"></result>
            <result property="courseName" column="course_name"></result>
            <result property="level" column="level"></result>
            <result property="color" column="color"></result>
            <result property="startTime" column="start_time"></result>
            <result property="endTime" column="end_time"></result>
        </collection>
    </resultMap>
    <!--查询周课程信息-->
    <select id="findStudioCourseWeekTable" parameterType="com.kawaxiaoyu.api.appointCourse.dto.CourseWeekTableDo"
            resultMap="weekCourseMap">
        SELECT manage_week_day.`week_id`,m.* FROM manage_week_day
            LEFT JOIN (SELECT wy_Client_user.`first_name`,manage_course.`course_name`,t.level,t.color,DAYOFWEEK(t.start_time)-1 week_day,DATE_FORMAT(t.start_time,'%H:%m') start_time,DATE_FORMAT(t.end_time,'%H:%m') end_time
            FROM
                (SELECT level,color,start_time,end_time,teacher_id,course_id FROM manage_course_table
                WHERE YEARWEEK( DATE_FORMAT(  start_time,'%Y-%m-%d' ),1 ) = YEARWEEK(NOW(),1)+(#{courseWeekTableDo.weekday}) AND STATUS=1 AND TYPE=#{courseWeekTableDo.type}) t
            LEFT JOIN wy_Client_user ON t.`teacher_id`=wy_Client_user.`login`
            LEFT JOIN manage_course ON manage_course.`id`=t.`course_id` WHERE manage_course.`studio_id`=#{courseWeekTableDo.studioId} ORDER BY t.start_time ASC) m ON manage_week_day.`week_id`=m.week_day
    </select>

 3.反思与总结

        1.mysql中YEARWEEK(时间)支持按照星期数进行查询,默认是周天是一周的第一天,所以想从周一开始查询,写法是YEARWEEK(时间,1),查询下周的数据:YEARWEEK(时间,1)+1,查询上周传-1,查询本周数据传0.

        2.对于数据库中查询指定时间内不存在数据但是返回参数中需要有指定时间的场景,暂时想到的就是添加一个天数表作为左连接查询的基础,这样能保证指定天数都会有数据返回,具体天数对应的数据根据业务实际需求进行返回,有则返回,没有则为空.创建的天数表仅作为组装参数使用.返回传参协议中周天没有数据就返回空集合:

 自己实现时想到的方案,如果大家有更好的方案可以评论区留言,不足之处可以指正!