########## 开始编写 SQL ##########
#将member表数据清空
#丢失数据可能是整条记录或只丢失积分现会员表中还存有部分数据,但积分可能不准确
deletefrom member;
#向member中插入card_id和creadits,
#card_id是user中为会员的card_id,
#sum(expense_money)是sales表中会员的所有消费,用sum是因为会员可能消费多次
#会员积分只对会员消费有效(消费1元为1个积分)
insert into member(card_id,creadits)
select user.card_id,sum(expense_money)from user,sales
where user.ismember=1 and user.name=sales.name
group by sales.name;
#只要为本店会员则在会员表中都会有记录,若无消费积分为0
insert into member(card_id,creadits)
select user.card_id,0from user
where user.ismember=1 and not exists(
select card_id from member where user.card_id=member.card_id);
#更新member表,会员积分 = 所有消费记录 - 退货记录
update member
inner join back
on member.card_id=back.card_id
set member.creadits=member.creadits-back.return_money;
#会员表的数据顺序按card_id升序显示(如果平台会自己输出可直接忽略)
select *from member
order by card_id;