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