sql:商品限购查询语句

来源:互联网 发布:unity3d里字母 编辑:程序博客网 时间:2024/03/29 06:10


如果用户没有购买过商品,可以看到这个VIP商品,如果用户购买过VIP商品,当购买总次数超过该商品的限购次数时不被显示。。。。

left join的含义是,和右表连接,显示左表所有数据,left join..on...后面的查询条件不管是否满足,都显示左表数据。

having是对分组数据的查询条件过滤,所以该语句可以实现需求

@Select(

"select pro.* from web_vip_activity_product pro left join web_mall_pay_log log  "
+ "on log.userId = #{userId} and log.source = 1 "
+ "and log.activityId = pro.activityId and log.mallProductId = pro.mallProductId "
+ "and pro.`activityId` = #{activityId}  group by pro.id having "
+ "case when "
+ "sum(log.count) = 0 or sum(log.count) is null "
+ "THEN "
+ "1=1 "
+ "else "
+ "sum(log.count) < pro.perLimitCount "
+ "end "
+ "order by pro.sort"
)
@Results({
@Result(property="mallProduct", column="mallProductId", one=@One(select="com.exdata.web.mapper.MallMapper.getMallProductById")),
})
public List<VipActivityProduct> listVipActivityProsForUser(@Param("userId")String userId,
@Param("activityId")int activityId) throws Exception;
0 0