2021-07-08

MYBATIS +MYSQL实现分组随机抽取具体数量数据以及使用 MYSQL 的自定义变量 @的问题解决

@Select({
            "<script>",
            "   <foreach item='extractExamPersonParam' collection='extractExamPersonParams' separator='union all'>",
            "       <foreach item='extractParam' collection='extractExamPersonParam.extractParams' separator='union all'>",
            "           SELECT T2.ID_CARD FROM (",
            "<![CDATA[",
            "               SELECT @E:=CASE WHEN @CUSTOMER_NO=T1.PRO_ORG_NO THEN @E+1 ELSE 1 END AS idCardCount, @CUSTOMER_NO:=T1.PRO_ORG_NO AS proOrgNo, T1.ID_CARD FROM",
            "               (SELECT I.ID_CARD, I.PRO_ORG_NO FROM GE_PERSON_INFO I,(SELECT @CUSTOMER_NO:= 0, @E:=1) r ]]>",
            "               WHERE I.TEAM_TYPE_CODE = #{extractExamPersonParam.teamTypeCode}",
            "       <choose>",
            "           <when test='extractExamPersonParam.teamTypeCode == \"01\" or extractExamPersonParam.teamTypeCode == \"03\"'>",
            "               AND I.POSITION_CODE = #{extractParam.positionPostCode}",
            "           </when>",
            "           <otherwise>",
            "               AND I.QUALIFICATION_POST = #{extractParam.positionPostCode}",
            "           </otherwise>",
            "       </choose>",
            "       AND I.PRO_ORG_NO IN ",
            "       <foreach item='orgNo' collection='extractExamPersonParam.orgNos' open='(' separator=',' close=')'>",
            "          #{orgNo}",
            "       </foreach>",
            "       ORDER BY I.PRO_ORG_NO, RAND()) T1 ) T2 WHERE idCardCount &lt;= #{extractParam.extractCount}",
            "       </foreach>",
            "   </foreach>",
            "</script>"
    })
    List<String> extractExamPerson(@Param("extractExamPersonParams") List<ExtractExamPersonParam> extractExamPersonParams);

@Data
@Accessors(chain = true)
public class ExtractExamPersonParam {
    private String teamTypeCode;

    private List<String> orgNos;
    
    private List<ExtractParam> extractParams;

    @Data
    @AllArgsConstructor
    public static class ExtractParam {
        private String positionPostCode;

        private Integer extractCount;
    }
}

自定义变量需要使用"<![CDATA[ ]]>"包住。
自定义变量声明参考Mysql生成序号方式。

参考链接