<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sundot.airport.exam.mapper.DailyTaskDetailMapper">

    <!-- 统计用户在指定时间范围内的错题数（按二级分类） -->
    <select id="countWrongAnswersByModule" resultType="com.sundot.airport.common.dto.ModuleStatisticsDTO">
        SELECT dtd.dtd_module_id   AS moduleId,
               dtd.dtd_module_name AS moduleName,
               COUNT(*)            AS wrongCount
        FROM edu_cs_daily_task_detail dtd
                 INNER JOIN edu_cs_daily_task dt ON dtd.dtd_task_id = dt.dt_id
        WHERE dt.dt_user_id = #{userId}
          AND dt.dt_business_date &gt;= #{startDate}
          AND dt.dt_business_date &lt;= #{endDate}
          AND dtd.dtd_is_correct = 0
          AND dt.del_status = 0
          AND dtd.del_status = 0
        GROUP BY dtd.dtd_module_id, dtd.dtd_module_name
    </select>

    <!-- 按题目分类统计全站正确率（关联edu_cs_ques和edu_cs_ques_cat） -->
    <select id="selectCorrectRateByCategory" resultType="com.sundot.airport.common.dto.CategoryAccuracyItemDto">
        SELECT qc.qc_name                                              AS categoryName,
               SUM(CASE WHEN dtd.dtd_is_correct = 1 THEN 1 ELSE 0 END) AS correctCount,
               COUNT(*)                                                AS totalCount,
               ROUND(
                       SUM(CASE WHEN dtd.dtd_is_correct = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
                       2
               )                                                       AS correctRate
        FROM edu_cs_daily_task_detail dtd
                 INNER JOIN edu_cs_daily_task dt ON dtd.dtd_task_id = dt.dt_id
                 INNER JOIN edu_cs_ques q ON dtd.dtd_ques_id = q.qu_id
                 INNER JOIN edu_cs_ques_cat qc ON q.qc_id = qc.qc_id
        WHERE dt.dt_business_date &gt;= #{startDate}
          AND dt.dt_business_date &lt;= #{endDate}
          AND dtd.dtd_is_correct IS NOT NULL
          AND dt.del_status = 0
          AND dtd.del_status = 0
          AND q.del_status = 0
          AND qc.del_status = 0
        GROUP BY qc.qc_id, qc.qc_name
        ORDER BY correctRate DESC
    </select>
</mapper>
