1, Note the use of sql relationship symbols such as > =, < =, < and so on

The following code:

<select id="queryGoodsList" resultType="com.sanbang.vo.GoodsInfo" parameterType="java.util.Map">
		select g.id,
		g.name,
		IFNULL((select s.salePrice from ezs_goods_audit_process s where s.goods_id = g.id and s.deleteStatus = '0'),0.00) as price,
		g.inventory,
		CONCAT("https://m.ezaisheng.com/",REPLACE(a.path,'\\','/'),"/",a.name) picturePath,
		d1.name colorName,
		ar.areaName areaName,
		d2.name utilName,
		g.area_id ,
		(select IFNULL(SUM(gc.count),0) from ezs_orderform ef LEFT JOIN ezs_goodscart gc on ef.id = gc.of_id where ef.deleteStatus = '0' and gc.goods_id = g.id and (ef.order_status BETWEEN 50 AND 110 and ef.order_status &lt;&gt; 100)) as salecount 
		from ezs_goods g,ezs_accessory a,ezs_dict d1,ezs_area ar,ezs_dict d2
		where g.goods_main_photo_id = a.id and g.color_id = d1.id and g.area_id = ar.id and g.util_id = d2.id AND g.`status`=2 and g.deleteStatus = '0'
		<if test="sales != null and sales == 1">
			ORDER BY salecount DESC
		</if>
	    limit #{pageStart},#{pageSize}
	</select>

Note:

and (ef.order_status &gt;= 50 and  ef.order_status &lt;= 110 and ef.order_status <> 100)

You are not asked in SQL, and an error will be reported in mybats.

Where BETWEEN 50 AND 110 can be used instead of ef.order'u status & gt; = 50 and ef.order'u status & lt; = 110

The following modes can also be used:

and (ef.order_status BETWEEN 50 AND 110 and ef.order_status &lt;&gt; 100)

If you use & gt; = to replace > =, & lt; = to replace < =, or & lt;> to replace < >, you cannot compile without it.

Note that between and contains boundary value intervals such as [2,4]

2, Use multiple foreach tags for the same field in the sql fragment

<select id="getPriceTrendcyNew" resultMap="PriceTrendcyNewResultMap">
    select
	IFNULL(AVG(a.price),0) currentPrice,
	a.data_time dealDate,
	gc.name className,
	(select area.areaName from ezs_area area where area.id =  a.region_id) areaName ,
	a.region_id,
	(
	select IFNULL(AVG(p.price),0)
	from ezs_price_trend p where p.status = '2' and p.goodClass2_id = a.goodClass2_id and date_add(DATE_FORMAT(p.data_time,'%Y-%m-%d'),interval 1 day) = DATE_FORMAT(a.data_time,'%Y-%m-%d')
      <if test="areaIds != null and areaIds.size > 0 " >
        and p.region_id 
		<foreach collection="areaIds" index="index" item="areaIds_1" open="in (" close=")" separator=",">
         #{areaIds_1}
     	</foreach>
      </if>
 	GROUP BY DATE_FORMAT(p.data_time,'%Y-%m-%d')
	) prePrice
	from ezs_price_trend a LEFT JOIN ezs_goods_class gc on a.goodClass2_id = gc.id
	where  a.status = '2'
      <if test="kindId != null" >
        and a.goodClass2_id = #{kindId}
      </if>
      <if test="areaIds != null and areaIds.size > 0 " >
        and a.region_id 
		<foreach collection="areaIds" index="index" item="areaIds_2" open="in (" close=")" separator=",">
         #{areaIds_2}
     	</foreach>
      </if>
      <!-- Screening criteria: one week, one month, one quarter, one year -->
      <choose>
      	<when test="dateBetweenType != null and dateBetweenType == 'WEEK' ">
      		and DATE_FORMAT(a.data_time,'%Y-%m-%d') > DATE_ADD(NOW(),INTERVAL -1 WEEK)
      	</when>
      	<when test="dateBetweenType != null and dateBetweenType == 'MONTH' ">
      		and DATE_FORMAT(a.data_time,'%Y-%m-%d') > DATE_ADD(NOW(),INTERVAL -1 MONTH)
      	</when>
      	<when test="dateBetweenType != null and dateBetweenType == 'QUARTER' ">
      		and DATE_FORMAT(a.data_time,'%Y-%m-%d') > DATE_ADD(NOW(),INTERVAL -1 QUARTER)
      	</when>
      	<when test="dateBetweenType != null and dateBetweenType == 'YEAR' ">
      		and DATE_FORMAT(a.data_time,'%Y-%m-%d') > DATE_ADD(NOW(),INTERVAL -1 YEAR)
      	</when>
      	<otherwise>
      		<!-- and DATE_FORMAT(a.data_time,'%Y-%m-%d') > DATE_ADD(NOW(),INTERVAL -1 MONTH) -->
      		and DATE_FORMAT(a.data_time,'%Y-%m-%d') > DATE_ADD(NOW(),INTERVAL -1 MONTH)
      	</otherwise>
      </choose>
 	GROUP BY DATE_FORMAT(a.data_time,'%Y-%m-%d') ORDER BY DATE_FORMAT(a.data_time,'%Y-%m-%d') desc <!-- LIMIT #{startPos},#{pageSize} -->
  </select>

Among them:

and p.region_id 
		<foreach collection="areaIds" index="index" item="areaIds_1" open="in (" close=")" separator=",">
         #{areaIds_1}
     	</foreach>

And:

 and a.region_id 
		<foreach collection="areaIds" index="index" item="areaIds_2" open="in (" close=")" separator=",">
         #{areaIds_2}
     	</foreach>

The second segment is to filter the region ID of the parent query. The first segment is to filter the region ID of the child query. The set areaIds in the incoming mapper is one, and the item s need to be distinguished, for example, areaIds 2, areaIds 1.

Pay attention to the use of two foreach tags in it!!!!