全智通A+常见问题汇总解答—A+会员充值明细页面删除失败
错误描述:会员充值明细页面点击删除充值单,提示删除失败
错误日志:
【错误SQL语句】:update t_member_product set amount=amount-(select amount from t_member_chargproduct where document_id='HC180820002' and product_id=t_member_product.product_id) ,amount_remain=amount_remain-(select amount from t_member_chargproduct where document_id='HC180820002' and product_id=t_member_product.product_id) where member_id=(select member_bid from t_member_charg where document_id='HC180820002') and product_id in (select product_id from t_member_chargproduct where document_id='HC180820002' and amount>0) and paysort='计次消费'
错误原因:历史版本导致会员充值允许充重复的项目和货品,删除充值单据时,后台子查询:一个充值单不会存在两条重复的项目编号或者货品编号。
解决方案:
查询重复项目和货品
select max(id) as id,member_id,ritem_id from t_member_ritem group by member_id,ritem_id HAVING count(ritem_id)>1
select max(id) as id,member_id,product_id from t_member_product group by member_id,product_id HAVING count(product_id)>1
select max(id) as id,document_id,ritem_id from t_member_chargritem group by document_id,ritem_id HAVING count(ritem_id)>1
select max(id) as id,document_id,product_id from t_member_chargproduct group by document_id,product_id HAVING count(product_id)>1
注意:删除前请查询重复项目和货品是否有疑问
--删除重复会员卡项目
delete from t_member_ritem where id in (
select max(id) as id from t_member_ritem
group by member_id,ritem_id HAVING count(ritem_id)>1)--删除重复会员卡货品
delete from t_member_product where id in (
select max(id) as id from t_member_product
group by member_id,product_id HAVING count(product_id)>1)--删除重复会员卡充值项目明细
delete from t_member_chargritem where id in (
select max(id) as id from t_member_chargritem
group by document_id,ritem_id HAVING count(ritem_id)>1)--删除重复会员卡充值货品明细
delete from t_member_chargproduct where id in (
select max(id) as id from t_member_chargproduct
group by document_id,product_id HAVING count(product_id)>1)