全智通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)