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

最新推荐文章于 2025-04-30 23:00:54 发布

lycIT

最新推荐文章于 2025-04-30 23:00:54 发布

阅读量4.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mybatis

本文链接：https://blog.csdn.net/lycIT/article/details/78879911

mybatis 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了一种在MyBatis中实现复杂SQL查询的方法，特别是如何通过XML映射文件来处理带有特殊字符的SQL语句，利用CDATA段确保SQL语法正确解析。此外，还提供了一个复杂的SQL查询示例，该示例能够高效地统计不同月份订单的数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如在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