商品有上限购次数,比如为5次,则每个用户最多只能买这个商品5次,
需求:用户购买次数达到限购次数的话,在用户查看商品列表的时候,不显示该商品
写查询商品列表的语句如下:
select pro.* from web_vip_activity_product pro
join web_mall_pay_log logon log.userId = '00304490ec2f48959f98dc2e06783403'
and log.source = 1
and log.activityId = pro.activityId
and log.mallProductId = pro.mallProductId
group by pro.id having sum(log.count) < pro.perLimitCount
ps:如果去掉 group by pro.id 语句,直接用having, 也可以查询,默认是语句自动加了group by null
但这样的查询结果是不对的。。。。。
--------------------------------------------------
之前写的错误语句是:
select pro.* from web_vip_activity_product pro
join web_mall_pay_log logon log.userId = '00304490ec2f48959f98dc2e06783403'
and log.source = 1
and log.activityId = pro.activityId
and log.mallProductId = pro.mallProductId
and sum(log.count) < pro.perLimitCount
错误原因,自己理解的,sum为聚合函数,不能作为查询条件,但可以放到查询结果里,
改进方法可以改为子查询,但是这样很麻烦,如下:
select * from (
select pro.* ,sum(log.count) a, pro.perLimitCount b from web_vip_activity_product pro
join web_mall_pay_log log
on log.userId = '00304490ec2f48959f98dc2e06783403'
and log.source = 1
and log.activityId = pro.activityId
and log.mallProductId = pro.mallProductId )c where a < b
查询字段如下:
因为要查商品所有字段,无法将子查询里的pro.* 赋予一个别名供最外层的查询使用(会报错的),所以放弃使用子查询方法
最终版语句:
select pro.* from web_vip_activity_product pro
join web_mall_pay_log log
join web_user_account account join web_mall_product mallpro
on account.userId = '00304490ec2f48959f98dc2e06783403' and mallpro.id = pro.mallProductId and account.vipLevel <= mallpro.vipLevel
and log.userId = '00304490ec2f48959f98dc2e06783403'
and log.source = 1
and log.activityId = pro.activityId
and log.mallProductId = pro.mallProductId
group by pro.id having sum(log.count) < pro.perLimitCount
and pro.`sort` = 1