##多表联查
<select id="findByProductName" resultType="com.test.MyProductInfo">
select
p.id,
p.product_name as productName,
ps.sales_volumes as salesVolumes,
sp.my_price as myPrice,
sp.distribution_amount as distributionAmount,
sp.cost
from
gms_product p,
gms_product_sale ps,
gms_store_product sp
where
p.sn=ps.product_sn and sp.product_sn=p.sn
<if test="storeSn !=null">
and p.store_sn=#{storeSn}
</if>
<if test="productSn !=null">
and ps.product_sn=#{productSn}
</if>
</select>
##多表联查根据编号集合查询所需产品信息
<select id="findByProductSnList" parameterType="java.util.List" resultMap="StoreHomepageProductMap">
select
p.product_name,
p.product_specification,
p.photo,
ps.sales_volumes,
sp.amount
from
gms_product p,
gms_product_sale ps,
gms_store_product sp
where p.sn=ps.product_sn
and ps.product_sn=sp.product_sn
and ps.product_sn in
<foreach collection="productSn" index="index" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
##多表联查模糊查询
<select id="findByProductName" resultType="com.test.MyProductInfo">
select
p.id,
p.product_name as productName,
ps.sales_volumes as salesVolumes,
sp.my_price as myPrice,
sp.distribution_amount as distributionAmount,
sp.cost
from
gms_product p,
gms_product_sale ps,
gms_store_product sp
where
p.sn=ps.product_sn and sp.product_sn=p.sn
<if test="storeSn !=null">
and p.store_sn=#{storeSn}
</if>
<if test="productName != null">
AND p.product_name like concat('%',concat(#{productName}),'%')
</if>
</select>
##模糊查询查询不出数据问题
模糊查询改成这样
product_name like concat('%',concat(#{productName}),'%')