问题描述:在会员卡型页面,修改某个会员卡类别,编辑页面加载数据失败
问题截图:
数据库截图:
查询会员卡积分兑换规则明细SQL语句
SQL语句:
Select id ,popularize_orgid, money=isnull(money,0) ,
discount_money=isnull(discount_money,0) ,
sale_money= (case when card_type='套餐卡'
then menuship else membership end )+isnull(money,0)-isnull(discount_money,0),
sort_name ,integral ,integral_change ,discount_ritem ,discount_product ,
membership ,menuship ,value_day ,rem ,membership_rights ,useDescription ,
clear_amount ,card_type ,discount_carclear ,maxfavorable_item ,maxfavorable_product ,
remind_lowmoney ,ispopularize ,pic_path ,
(Select integral_value From t_member_integralrule
Where sort_name = t_member_sort.sort_name And consume_type = '销售')
As 'Sale',
(Select integral_value From t_member_integralrule
Where sort_name = t_member_sort.sort_name And consume_type = '洗车')
As 'Wash', (Select integral_value From t_member_integralrule
Where sort_name = t_member_sort.sort_name And consume_type = '充值')
As 'Charg'
From t_member_sort
Where sort_name = '洗车卡'
错误版本号:V6.18.1025
错误原因:
会员卡积分兑换规则明细中有重复数据(重复数据不止一条),影响子查询。
数据来源,分析过程:
1、修改会员卡后台保存时,会删除积分兑换规则明细,
在新建会员卡型时,不会删除再重建
2、除了积分兑换规则明细之外,项目和货品没有重复数据,会员卡型主表只有一条数据。
解决方案:
--删除重复会员卡积分兑换规则明细
delete from t_member_integralrule where id in (
select a.id from t_member_integralrule a
left join (
select min(id) as id,sort_name,consume_type
from t_member_integralrule
group by sort_name,consume_type
having count(id) > 1) b
on a.sort_name=b.sort_name and a.consume_type=b.consume_type
and a.id!=b.id where b.id is not null)
先关内容:后期版本会在系统自检中增加删除历史重复数据