<?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.template.mapper.SmartRelationMapper">

    <select id="getList" resultType="com.template.model.vo.SmartRelationVo">
        SELECT
        (SELECT SUM(count)
        FROM `smart_relation`
        WHERE user_id = sr.user_id
        and relation_id = sr.relation_id
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and date_time &gt;= #{startTime} and date_time &lt; #{endTime}
        </if>
        ) as count,
        sr.relation_id as relationId,
        sr.relation_name as relationName,
        sc.`name` as className,
        sg.`name` as gradeName
        FROM
        `smart_relation` sr
        LEFT JOIN smart_user su
        on su.id=sr.relation_id
        LEFT JOIN smart_class sc on su.school_class=sc.id
        LEFT JOIN smart_grade sg on sc.grade_id=sg.id
        WHERE sr.user_id=#{userId}
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and sr.date_time &gt;= #{startTime} and sr.date_time &lt; #{endTime}
        </if>
        GROUP BY sr.relation_id
        ORDER BY count DESC,CONVERT(sr.relation_name USING gbk) COLLATE gbk_chinese_ci

    </select>
    <select id="getAppList" resultType="com.template.model.vo.SmartRelationAppVo">
        SELECT
        (SELECT SUM(count)
        FROM `smart_relation`
        WHERE user_id = sr.user_id
        and relation_id = sr.relation_id
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and date_time &gt;= #{startTime} and date_time &lt; #{endTime}
        </if>
        ) as count,
        sr.relation_id as relationId,
        sr.relation_name as relationName,
        sc.`name` as className,
        su.card_no as cardNo,
        su.head_image as headImage
        FROM
        `smart_relation` sr
        LEFT JOIN smart_user su
        on su.id=sr.relation_id
        LEFT JOIN smart_class sc on su.school_class=sc.id
        WHERE sr.user_id=#{userId}
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and sr.date_time &gt;= #{startTime} and sr.date_time &lt; #{endTime}
        </if>
        GROUP BY sr.relation_id
        ORDER BY count DESC,CONVERT(sr.relation_name USING gbk) COLLATE gbk_chinese_ci
    </select>
</mapper>
