需求:查询用户的学习的情况,应该学习的课程数量,实际学习了课程的数量;
数据源:
t_sys_user 用户基础信息表(每个用户可以绑定一个车辆类型 car_type_id)
t_car_type 车辆类型表
t_company 公司信息表
t_driver 司机信息表(与用户信息表相关联 user_id)
t_course 课程表(与车辆类型表相关联 car_type_id)
t_course_user 用户学习记录表(关联课程表 状态:1.未开始 2.已开始 3.完成)
思路:先从t_sys_user 用户基础信息表中 获取对应的车辆类型,每个车辆类型绑定了所需要学习的课程,t_course_user 用户学习记录表 记录了用户学习的课程以及学习课程的状态,那就可以获取到学习了的课程数量,需要学习的课程数量不为零,学习了的课程数量为零那么为未开始状态;学习了的课程数量不为零,且小于需要学习的课程数量,那么为学习中;相等为已完成状态;
实现:这里采用的是直接一步到位,通过一个嵌套SQL把数据查询出来,当然也可以不用SQL,通过它们的关联关系用MyBatis-Plus类库的方法查询出来,但是过程就要复杂的多;
Mapper层:
IPage<CourseUserDao> undone(@Param("page") PeakPage<CourseUserParam> page, @Param("companyId") String companyId);
XML:
SELECT aaa.nick_name as nickName,aaa.deptName as deptName,aaa.score as score,aaa.carTypeName as carTypeName,
aaa.numberSum as numberSum,aaa.leanNumber as leanNumber,aaa.login_name as loginName
from (
SELECT a.*,b.`names` as carTypeName ,c.company_name as deptName,d.newest_score as score,
(
SELECT COUNT(aa.id) from t_course aa where aa.deleted=0 and aa.car_type_id=a.card_type_id and aa.type=1
) as numberSum,
(
SELECT COUNT(bb.id) from t_course_user bb where bb.deleted=0 and bb.car_type_id=a.card_type_id and bb.user_id=a.id and bb.state=1
) as leanNumber
from t_sys_user a
LEFT JOIN t_car_type b on a.card_type_id=b.id
LEFT JOIN t_company c on c.id=a.department_id
LEFT JOIN t_driver d on d.user_id=a.id
where a.client_type like '%APP%'
and a.deleted=0 and c.deleted=0
<if test="companyId !=null and companyId != '' and companyId !='0'.toString()">
AND a.company_id = #{companyId}
</if>
<if test="page.search != null">
<if test="page.search.userName != null and page.search.userName != ''">
and a.nick_name like concat('%',#{page.search.userName},'%')
</if>
<if test="page.search.loginName != null and page.search.loginName != ''">
and a.login_name like concat('%',#{page.search.loginName},'%')
</if>
</if>
) aaa
<where>
<if test="page.search != null">
<if test="page.search.state != null and page.search.state != '' and page.search.state =='1'.toString() ">
aaa.leanNumber=0 and aaa.numberSum!=0
</if>
<if test="page.search.state != null and page.search.state != '' and page.search.state =='2'.toString() ">
aaa.numberSum > aaa.leanNumber and aaa.leanNumber!=0
</if>
<if test="page.search.state != null and page.search.state != '' and page.search.state =='3'.toString() ">
aaa.numberSum = aaa.leanNumber
</if>
</if>
</where>
</select>