@Select("<script>" +
"select o.id id,COUNT(o.id) num,o.name name from (\n" +
"SELECT n.id id,n.REAL_NAME name,m.createtime\n" +
"FROM\n" +
"\t(\n" +
"SELECT CREATEUSER,TO_CHAR( CREATETIME, ‘yyyy-mm-dd‘ ) createtime \n" +
"FROM YCNY_USER_ROUTES \n" +
"WHERE CREATETIME BETWEEN TO_DATE(#{beginTime}, ‘YYYY-MM-DD HH24:MI:SS‘) AND TO_DATE(#{endTime}, ‘YYYY-MM-DD HH24:MI:SS‘)\n" +
"AND ISDEL = 0 AND CREATEUSER IN " +
"<foreach item=‘id‘ index=‘index‘ collection=‘idList‘ open=‘(‘ separator=‘,‘ close=‘)‘>" +
"#{id}\n" +
"</foreach>" +
"GROUP BY CREATEUSER,TO_CHAR( CREATETIME, ‘yyyy-mm-dd‘ )\n" +
") m\n" +
"\tLEFT JOIN TIN_HR_USERS n ON m.CREATEUSER = n.ID\n" +
") o\n" +
"GROUP BY o.id,o.name" +
"</script>")
List<UserRoutesVo> findByCreatetimeAndCreateUser(@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("idList")List<String> idList);

拓展：

Mapper中自写SQl查询语句（IN）