新闻动态

删除会员卡(V6系列、Z7系列、V9商业、V10系列)

2019-03-04 21:50:17

删除卡的操作:

[注:确认无误时才可以操作,删除的卡永远也找不回来的,使用时请慎重]

A、删除所有的会员卡(不管有没有发行把所有会员卡都删了)

truncate table pos_t_vip_flow
truncate table pos_t_vip_info

B、删除所有未发行的卡

alter table pos_T_vip_info disable trigger all
delete from pos_T_vip_info where card_status='3'
alter table pos_T_vip_info enable trigger all
alter table pos_T_vip_flow disable trigger all
delete from pos_T_vip_flow where card_id not in(select card_id from pos_T_vip_info)
alter table pos_T_vip_flow enable trigger all

C、删除某张会员卡

alter table pos_T_vip_info disable trigger all
delete from pos_T_vip_info where card_id='会员卡号'
alter table pos_T_vip_info enable trigger all
alter table pos_T_vip_flow disable trigger all
delete from pos_T_vip_flow where card_id='会员卡号'
alter table pos_T_vip_flow enable trigger all

D、删除某一个会员类别的未发行的卡

alter table pos_T_vip_info disable trigger all
delete from pos_T_vip_info where card_status='3' and card_type='两位会员分类编号'
alter table pos_T_vip_info enable trigger all
alter table pos_T_vip_flow disable trigger all
delete from pos_T_vip_flow where card_id not in(select card_id from pos_T_vip_info)
alter table pos_T_vip_flow enable trigger all

E、删除某一个会员类别里的所有卡(不管有没有发行)

alter table pos_T_vip_info disable trigger all
delete from pos_T_vip_info where card_type='两位会员分类编号'
alter table pos_T_vip_info enable trigger all
alter table pos_T_vip_flow disable trigger all
delete from pos_T_vip_flow where card_id not in(select card_id from pos_T_vip_info)
alter table pos_T_vip_flow enable trigger all

F、

删除所有会员卡号最后一位数是4, 7的卡:

alter table pos_T_vip_info disable trigger all
delete from pos_T_vip_info where right(card_id,1) in('4','7')
alter table pos_T_vip_info enable trigger all
alter table pos_T_vip_flow disable trigger all
delete from pos_T_vip_flow where card_id not in(select card_id from pos_T_vip_info)
alter table pos_T_vip_flow enable trigger all


G、删除从xxx 开始到xxxx结束的卡:

alter table pos_T_vip_info disable trigger all
delete from pos_T_vip_info where card_id between '开始卡号' and '结束卡号'
alter table pos_T_vip_info enable trigger all
alter table pos_T_vip_flow disable trigger all
delete from pos_T_vip_flow where card_id not in(select card_id from pos_T_vip_info)
alter table pos_T_vip_flow enable trigger all

H、
删除2012-01-01之后没有发生过业务(例如:消费、冲值、积分等)的会员
注意:会员卡删掉了再也找不回来了,所以用这个语句需慎重考虑

alter table pos_T_vip_info disable trigger all
delete from pos_t_vip_info where card_id not in(select card_id from pos_t_vip_flow where convert(char(10),oper_date,120)>='2012-01-01' group by card_id)
alter table pos_T_vip_info enable trigger all
alter table pos_T_vip_flow disable trigger all
delete from pos_T_vip_flow where card_id not in(select card_id from pos_T_vip_info)
alter table pos_T_vip_flow enable trigger all