Mybatis复杂sql总结

1.mybatis中插入list参数，举例：

sql层级的需求是：在in中插入的list

sql代码如下：(当然这个函数也要注意mysql的LPAD函数的学习，以及关于时间函数的学习)

SELECT r.`INDI_NO`,LPAD(HOUR(r.`DATA_DATE`),2,0) hours,ROUND(SUM(ABS(r.`RVAL`)),2) AS rval
        FROM r_data_currxxx r
        WHERE r.`MS_NO`
        IN ('123','456','789','134','135')
        AND r.`INDI_NO` = 'B003'
        AND MINUTE(r.`DATA_DATE`) = 0
        AND SECOND(r.`DATA_DATE`) = 0
        AND DATE_FORMAT(r.DATA_DATE,'%Y%m%d') = DATE_FORMAT(NOW(),'%Y%m%d')
        GROUP BY HOUR(r.`DATA_DATE`)
        ORDER BY hours

代码中，in中的几个参数就是在mybatis中传入的list，那么mybatis层级该如何书写呢？上代码

 SELECT r.`INDI_NO`,LPAD(HOUR(r.`DATA_DATE`),2,0) hours,ROUND(SUM(ABS(r.`RVAL`)),2) AS rval
        FROM r_data_currxxx r
        WHERE r.`MS_NO`
        IN
        <foreach collection="list" item="list" open="(" separator="," close=")" index="index">
            #{list}
        </foreach>
        AND r.`INDI_NO` = #{xxxx}
        AND MINUTE(r.`DATA_DATE`) = 0
        AND SECOND(r.`DATA_DATE`) = 0
        AND DATE_FORMAT(r.DATA_DATE,'%Y%m%d') = DATE_FORMAT(NOW(),'%Y%m%d')
        GROUP BY HOUR(r.`DATA_DATE`)
        ORDER BY hours

 List<Map<String,Object>> selectPowder(@Param("list") List<String> list,@Param("xxxx") String xxxx);