Mybatis查询：实现对重复数据的分组并计数

记录：Mybatis: 中返回结果集resultMap，表示的是对应关系，在这里我们可以给查询到的结果取别名，实现与Entity中的类相对应

• column对应数据库中列名或者你自己起的别名：查询到的结果集的列名
• property对应Entity中的类的变量名
• 只有对应起来才能正确映射

    <resultMap id="CheckRecordVoMap" type="com.gwm.lms.asm.checkoutrecord.entity.CheckRecordVo">
        <result column="EBOARD_DATETIME" jdbcType="VARCHAR" property="dateTime"/>
        <result column="EBOARD_PRODUCTCODE" jdbcType="VARCHAR" property="productCode"/>
        <result column="EBOARD_PRODUCTNAME" jdbcType="VARCHAR" property="productName"/>
        <result column="SUMNUMBER" jdbcType="DECIMAL" property="number"/>
        <result column="WAITCHECKNUMBER" jdbcType="DECIMAL" property="waitCheckNumber"/>
        <result column="QUALIFIEDNUMBER" jdbcType="DECIMAL" property="qualifiedNumber"/>
        <result column="NOTQUALIFIEDNUMBER" jdbcType="DECIMAL" property="notQualifiedNumber"/>
    </resultMap>

    <select id="selectCheckRecordVo" resultMap="CheckRecordVoMap" parameterType="com.gwm.lms.asm.checkoutrecord.entity.EBoardEntity">
        SELECT EBOARD_DATETIME,EBOARD_PRODUCTCODE,EBOARD_PRODUCTNAME,COUNT(*) as SUMNUMBER,
        sum(case EBOARD_CHECKSTATUS when 1 then 1 else 0 end ) as WAITCHECKNUMBER,
        sum(case when EBOARD_CHECKSTATUS IN (2,3) then 1 else 0 end ) as WAITCHECKNUMBER,
        sum(case EBOARD_CHECKSTATUS when 4 then 1 else 0 end ) as NOTQUALIFIEDNUMBER
        FROM SM_AFTERSALE_EBOARD
        <where>
            <trim>
                <if test="productCode!= null and productCode!= ''">
                    and regexp_like(EBOARD_PRODUCTCODE ,#{productCode},'i')
                </if>
                <if test="productName!= null and productName!= ''">
                    and regexp_like(EBOARD_PRODUCTNAME ,#{productName},'i')
                </if>
                <if test="startTime!= null and startTime!= ''">
                    <![CDATA[ and EBOARD_DATETIME >= to_date(#{startTime},'yyyy-MM-dd HH24:mi:ss') ]]>
                </if>
                <if test="endTime!= null and endTime!= ''">
                    <![CDATA[ and EBOARD_DATETIME < to_date(#{endTime},'yyyy-MM-dd HH24:mi:ss')+1 ]]>
                </if>
            </trim>
        </where>
        GROUP BY EBOARD_DATETIME,EBOARD_PRODUCTCODE,EBOARD_PRODUCTNAME
    </select>

GROUP BY 后的条件一定要包含 SELECT 后的所有非结果集。

这里实现了对重复数据计数，并根据不同状态进行分类。