<?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.diagbot.mapper.QuestionUsualMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.diagbot.entity.QuestionUsual">
        <id column="id" property="id" />
        <result column="is_deleted" property="isDeleted" />
        <result column="gmt_create" property="gmtCreate" />
        <result column="gmt_modified" property="gmtModified" />
        <result column="creator" property="creator" />
        <result column="modifier" property="modifier" />
        <result column="question_id" property="questionId" />
        <result column="dept_id" property="deptId" />
        <result column="order_no" property="orderNo" />
        <result column="remark" property="remark" />
    </resultMap>

    <select id="getQuestionUsualName" resultType="com.diagbot.dto.QuestionUsualDTO">
        SELECT b.id as question_id, b.`name` FROM `icss_dept_info` a
        LEFT JOIN `icss_question_usual` c ON a.id = c.dept_id
        LEFT JOIN `icss_question_info` b ON b.id = c.question_id
        WHERE a.is_deleted = 'N' AND b.is_deleted = 'N' AND c.is_deleted = 'N' AND a.id = #{deptId}
        <if test="age != null and age != ''">
            <![CDATA[ and b.age_begin <= #{age} ]]>
            <![CDATA[ and b.age_end >= #{age} ]]>
        </if>
        <if test="sexType == 3">
            and b.sex_type in ('1','2','3')
        </if>
        <if test="sexType != 3">
            and b.sex_type in ('3',#{sexType})
        </if>
        order by c.order_no
    </select>
</mapper>
