mysql 查询将users.USER_NAME （0001,002,003）转换成 USER_NAMES（张三，李四，王五）

SELECT SQL_CALC_FOUND_ROWS info.*FROM( 
select warn.RECODE,warn.WARN_SYSCODE,SYS_INFO.SYS_NAME,warn.WARN_CONTENT,GROUP_CONCAT(users.USER_NAME)as USER_NAMES, DATE_FORMAT(warn.WARN_TIME,'%Y-%m-%d %H:%i:%s') WARN_TIME,
(case warn.WARN_STATUS when 'Y' then '是' when 'N' then '否' else warn.WARN_STATUS end ) WARN_STATUSC from WARN_INFO warn left join USER_INFO users 
on FIND_IN_SET(users.USER_YIST , warn.WARN_SENDUSER) LEFT JOIN SYS_INFO ON warn.WARN_SYSCODE = SYS_INFO.SYS_CODE GROUP BY warn.RECODE ORDER BY warn.WARN_TIME DESC ) info
limit 10,10;
SELECT FOUND_ROWS() as total; 

 

将users.USER_NAME （0001,002,003）转换成 USER_NAMES（张三，李四，王五）

 

 

查询出分页前的总行数，如果要查分页后的总行数 则不要添加 SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS

SELECT FOUND_ROWS()

此时mybati的查询要返回两个结果集，如果返回类型为 HashMap的话 这样写 

<resultMap type="java.util.HashMap" id="listresult">
</resultMap>
<resultMap type="java.util.HashMap" id="totals">
</resultMap>
<select id="queryWarnDetail" resultMap ="listresult,totals" parameterType="java.util.HashMap">    

</select>