经典sql

<select id="selectJoinSkcBySkc" resultType="Map">
   SELECT
   t4.merchandise_id as skcId,
   count(1) as count
   FROM
   goods_sku t1,
   op_pos_item t2,
   op_pos_item t3,
   goods_sku t4,
   op_pos t5
   WHERE
   t1.merchandise_id = #{skcId}
   AND t1.id = t2.sku_id
   AND t2.pos_id = t3.pos_id
   and t3.sku_id = t4.id
   and t3.pos_id  = t5.id
   and t4.merchandise_id != #{skcId}
   <if test="shopIds !=null and shopIds.size()>0">
      and t5.shop_id in
      <foreach collection="shopIds" item="shopId" open="(" close=")" index="index" separator=",">
         #{shopId}
      </foreach>
   </if>
   group by t4.merchandise_id
   order by count desc
</select>

<select id="selectCategorySale" resultType="Map" parameterType="Map">
   SELECT
   t6.id as categoryId,
   CONCAT(t7.name,'-',t6.name) as category,
   sum(t2.price_actual) as priceActual,
   IFNULL(ROUND(sum(t2.price_actual)/sum(t2.price_list) * 100,2),0) as discount,
   sum(t2.quantity) as quantity
   FROM
   op_pos t1,
   op_pos_item t2,
   goods_sku t3,
   goods_merchandise t4,
   goods_product t5,
   goods_category t6 LEFT JOIN goods_category t7 on t6.parent_id = t7.id
   WHERE
   t1.id = t2.pos_id 
   AND t2.sku_id = t3.id
   AND t3.merchandise_id = t4.id and t4.`status` = 1 and t5.`status` = 1
   AND t4.product_id = t5.id
   AND t5.category_id = t6.id
   AND t1.shop_id = #{shopId}
   AND DATE_FORMAT(t1.bill_time,'%Y-%m-%d') BETWEEN
   DATE_FORMAT(#{startDate},'%Y-%m-%d') AND
   DATE_FORMAT(#{endDate},'%Y-%m-%d')
   group by category,t6.id 
</select>