mybatis 动态sql 多表分组查询_mybatis动态分组-优快云博客

本文介绍了在MyBatis中如何使用动态SQL进行多表分组查询，并强调了WHERE条件必须置于GROUP BY之前的重要性。同时，解释了在排序中DESC（降序）和ASC（升序）的概念及其应用。

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

废话不多说直接上例子：

<select id="selectBuildingMsgByTimeFromBeginToEndAndAreaName" parameterType="java.util.Map" resultType="com.pojo.BuildingPojo">
    SELECT b.props_and_uses_name as propsAndUsesName,
    COUNT(*) as constructionNumbers, 
    COUNT(b.building_area) as constructionTotalArea
    FROM tb_construction_info c
	LEFT JOIN tb_building_info b
	ON c.cid = b.cid
	<where> 
	<if test="beginTime != null or beginTime != '' and endTime!=null or endTime != ''">
		c.completion_date between #{beginTime,jdbcType=TIMESTAMP} and
		#{endTime,jdbcType=TIMESTAMP}
	</if>
	<if test="beginTime != null or beginTime != '' and beginTime==null or endTime != ''">
		AND c.completion_date > #{beginTime,jdbcType=TIMESTAMP} 
	</if>
	<if test="beginTime == null or beginTime == '' and endTime!=null or endTime!=''">
		AND c.completion_date  < #{endTime,jdbcType=TIMESTAMP} 
	</if>
	<if test="areaName != null and areaName != ''">
	 	AND c.area_name  = #{areaName,jdbcType=VARCHAR}
	</if>
	</where>
	GROUP BY b.props_and_uses_name
	ORDER BY c.completion_date DESC
	
  </select>

注意点：where 条件一定要放到group by 语句上面否则会报错。

ps：追加一下什么是DESC 什么是ASC

DESC：降序从大到小，从高到底

ASC：升序排序，从小到大，从底到高