mybatis和mysql中查询前六个月订单中每个月的总订单数

如在employeeMapper.xml中

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="cn.lz.life.dao.EmployeeDao">
	<resultMap id="map" type="HashMap">
		<result column="number" property="number" />
		<result column="monthName" property="monthName" />
	</resultMap>
	<select id="findOrderFormNum" resultType="map" parameterType="HashMap" >
		
		    SELECT 
		    COUNT(*) as number, MONTH(DATE_SUB(SYSDATE(),INTERVAL #{count} MONTH)) as monthName
		    FROM employee e, order_form o, employee_order_middle eom
		    WHERE e.employeeNo = eom.e_id
		    AND o.orderNo = eom.o_id
		    AND e.employeeNo = #{employeeNo}
		    AND o.finishTime <![CDATA[>=]]> DATE_SUB(DATE_FORMAT(SYSDATE(), '%Y-%m-01'),INTERVAL #{count} MONTH) 
			AND o.finishTime <![CDATA[<]]> DATE_SUB(DATE_FORMAT(SYSDATE(), '%Y-%m-01'),INTERVAL #{count}- 1 MONTH)
		
  </select>
</mapper>

在使用mybatis 时我们sql是写在xml 映射文件中，如果写的sql中有一些特殊的字符的话，在解析xml文件的时候会被转义，但我们不希望他被转义，所以我们要使用<![CDATA[ ]]>来解决

六种sql查询语句

SELECT 
	(
		SELECT COUNT(*) FROM order_form
		WHERE finishTime 
		LIKE CONCAT('%',YEAR(DATE_SUB(SYSDATE(),INTERVAL 0 MONTH)),'-',MONTH(DATE_SUB(SYSDATE(),INTERVAL 0 MONTH)),'%')
	) AS one, MONTH(DATE_SUB(SYSDATE(),INTERVAL 0 MONTH)) AS monthOne,
	(
		SELECT COUNT(*) FROM order_form 
		WHERE finishTime 
		LIKE CONCAT('%',SUBSTR(DATE_SUB(SYSDATE(),INTERVAL 1 MONTH), 1, 7),'%')
	) AS two, MONTH(DATE_SUB(SYSDATE(),INTERVAL 1 MONTH)) AS monthTwo,
	(
		SELECT COUNT(*) FROM order_form 
		WHERE finishTime 
		LIKE CONCAT('%',EXTRACT(YEAR FROM DATE_SUB(SYSDATE(),INTERVAL 2 MONTH)),'-',EXTRACT(MONTH FROM DATE_SUB(SYSDATE(),INTERVAL 2 MONTH)),'%')
	) AS three, MONTH(DATE_SUB(SYSDATE(),INTERVAL 2 MONTH)) AS monthThree,
	(
		SELECT COUNT(*) FROM order_form 
		WHERE finishTime 
		LIKE CONCAT('%',DATE_FORMAT(DATE_SUB(SYSDATE(),INTERVAL 3 MONTH),'%Y-%m'),'%')
	) AS four, MONTH(DATE_SUB(SYSDATE(),INTERVAL 3 MONTH)) AS monthFour,
	(
		SELECT COUNT(*) FROM order_form 
		WHERE finishTime > DATE_SUB(DATE_FORMAT(SYSDATE(), '%Y-%m-01'),INTERVAL 4 MONTH) 
		AND finishTime < DATE_SUB(DATE_FORMAT(SYSDATE(), '%Y-%m-01'),INTERVAL 3 MONTH)
	) AS five, MONTH(DATE_SUB(SYSDATE(),INTERVAL 4 MONTH)) AS monthFive,
	(
		SELECT COUNT(*) FROM order_form 
		WHERE finishTime 
		BETWEEN DATE_SUB(DATE_FORMAT(SYSDATE(), '%Y-%m-01'),INTERVAL 5 MONTH) 
		AND DATE_SUB(DATE_FORMAT(SYSDATE(), '%Y-%m-01'),INTERVAL 4 MONTH)
	) AS six, MONTH(DATE_SUB(SYSDATE(),INTERVAL 5 MONTH)) AS monthSix
FROM DUAL