需求:在业务员管理客户页面,需要展示所有客户信息,并且按客户的最近下单次数进行排序。
第一次写的代码如下
<select id="queryHelpbuyCustomers" parameterType="Map" resultType="Map">
select a.addressId,
a.shopName,
a.phone,
a.consignee,
a.detailAddress,
count(o.orderId) orderCount,
(select addTime from wy_orders wo where wo.addressId=a.addressId order by wo.addTime desc limit 0,1) as addTime
FROM wy_user_address a
LEFT JOIN wy_orders o on a.addressId=o.addressId
where a.userId=#{userId} and a.status!=-1 and a.flag=1
and o.status!=-1 and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[<=date(o.addTime)]]>
<if test="shopName!=null and shopName!=''">
and a.shopName like CONCAT('%',#{shopName},'%')
</if>
group by a.addressId
order by orderCount desc
</select>
查询出来发现,新建的客户,并没有没产生订单,所以订单表和客户表并没有关联。查询出来的只有产生关联的数据。
在网上查找答案才知道。
以非主表的字段作为查询条件:
1.当把条件加入到 join子句时,会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。
2.当把条件放到where子句时,会首先进行连接操作,然后使用where子句对连接后的行进行筛选。
于是修改为
<select id="queryHelpbuyCustomers" parameterType="Map" resultType="Map">
select a.addressId,
a.shopName,
a.phone,
a.consignee,
a.detailAddress,
count(o.orderId) orderCount,
(select addTime from wy_orders wo where wo.addressId=a.addressId order by wo.addTime desc limit 0,1) as addTime
FROM wy_user_address a
LEFT JOIN wy_orders o on a.addressId=o.addressId and o.status!=-1 and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[<=date(o.addTime)]]>
where a.userId=#{userId} and a.status!=-1 and a.flag=1
<if test="shopName!=null and shopName!=''">
and a.shopName like CONCAT('%',#{shopName},'%')
</if>
group by a.addressId
order by orderCount desc
</select>
问题解决。