<?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="org.springblade.capcancel.mapper.CapCancelNumMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="org.springblade.capcancel.entity.CapCancelNum">
        <result column="THEMONTH" property="themonth" />
        <result column="SPID" property="spid" />
        <result column="SPNAME" property="spname" />
        <result column="PROVINCE" property="province" />
        <result column="AREA" property="area" />
        <result column="CANCELNUM" property="cancelnum" />
        <result column="OUTPUT" property="output" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        THEMONTH, SPID, SPNAME, PROVINCE, AREA, CANCELNUM, OUTPUT
    </sql>

    <select id="getListByMonth" resultType="org.springblade.capcancel.vo.CapCancelNumVO">
        SELECT
        PROVINCE,spname,sum(CANCELNUM) as CANCELNUM
        FROM CAP_CANCEL_NUM where output = 1 and THEMONTH = #{month}
        GROUP BY PROVINCE,SPNAME
        ORDER BY PROVINCE
    </select>

    <select id="getSpnameList" resultType="java.lang.String">
        SELECT
            SPNAME,
            sum( cancelnum ) AS num
        FROM
            CAP_CANCEL_NUM
        GROUP BY
            SPNAME
        ORDER BY
            num DESC
    </select>

    <select id="getPivotList" resultType="java.util.LinkedHashMap">
        ${sql}
    </select>

</mapper>
