<?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.testor.module.hazard.dao.THazardLicenseSignatureRecordDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.hazard.model.domain.THazardLicenseSignatureRecord">
                <id column="id" property="id"/>
                <id column="plan_id" property="planId"/>
                <id column="license_id" property="licenseId"/>
                <id column="type" property="type"/>
                <id column="signatory" property="signatory"/>
                <id column="signature" property="signature"/>
                <id column="signature_location" property="signatureLocation"/>
                <id column="signature_date" property="signatureDate"/>
                <id column="type_name" property="typeName"/>
    </resultMap>
    <select id="selectLatestByPlanIdAndType"
            resultType="com.testor.module.hazard.model.domain.THazardLicenseSignatureRecord">
        WITH RankedRecords AS (
            SELECT
                id,
                plan_id,
                license_id,
                type,
                type_name,
                signatory,
                signature,
                signature_location,
                signature_date,
                status,
                create_by,
                create_date,
                update_by,
                update_date,
                remarks,
                ROW_NUMBER() OVER (PARTITION BY plan_id, type ORDER BY signature_date DESC) AS row_num
            FROM
                t_hazard_license_signature_record
            <where>
                <if test="record.planId != null and record.planId != ''">
                     plan_id = #{record.planId}
                </if>
                <if test="record.licenseId != null and record.licenseId != ''">
                      AND license_id = #{record.licenseId}
                </if>
                <if test="record.type != null and record.type != ''">
                    AND type = #{record.type}
                </if>
            </where>
        )
        SELECT
            id,
            plan_id,
            license_id,
            type,
            type_name,
            signatory,
            signature,
            signature_location,
            signature_date,
            status,
            create_by,
            create_date,
            update_by,
            update_date,
            remarks
        FROM
            RankedRecords
        WHERE
            row_num = 1;
    </select>
</mapper>