--找到会员卡重复的,保留其中一条数据。
--提示,操作前,请先备份数据库,请每个GO前执行,不能执行重复,顺序不能乱
use isszmvB
go
select * into t_rm_vip_infobak20170628 from t_rm_vip_info --备份数据
go
--创建自增列
alter table t_rm_vip_info add id int identity
go
--找到重复的卡号
select * into aacf from t_rm_vip_info where card_id in
(select card_id from t_rm_vip_info group by card_id having COUNT(card_id)>1) order by card_id
go
--在重复的卡号里找到最小的ID,并且放到临时表里备用 --根据card_id分类获取数据最小ID列表
SELECT * into cfmin FROM aacf
WHERE
id IN
(select min(id) from aacf
group by card_id)
go
--找到不是最小的ID的会员信息,即就是要删除的会员信息
select * into sccf from t_rm_vip_info where id not in(select id from cfmin)
go
--删除会员重复的会员数据,并且保留ID最小的会员,即取重复中的其中一条
delete from t_rm_vip_info where id in(select id from sccf)
go
--最后删除ID列
alter table t_rm_vip_info drop column id
go
--最后检查是不是还有重复的卡号,没有数据就是对的
select card_id from t_rm_vip_info group by card_id having COUNT(card_id)>1
--删除的ID在此表记录。可以查询对比
select * from sccf order by card_id
select * from t_rm_vip_info
where card_id in(select card_id from sccf) order by card_id
--提示,操作前,请先备份数据库,请每个GO前执行,不能执行重复,顺序不能乱
use isszmvB
go
select * into t_rm_vip_infobak20170628 from t_rm_vip_info --备份数据
go
--创建自增列
alter table t_rm_vip_info add id int identity
go
--找到重复的卡号
select * into aacf from t_rm_vip_info where card_id in
(select card_id from t_rm_vip_info group by card_id having COUNT(card_id)>1) order by card_id
go
--在重复的卡号里找到最小的ID,并且放到临时表里备用 --根据card_id分类获取数据最小ID列表
SELECT * into cfmin FROM aacf
WHERE
id IN
(select min(id) from aacf
group by card_id)
go
--找到不是最小的ID的会员信息,即就是要删除的会员信息
select * into sccf from t_rm_vip_info where id not in(select id from cfmin)
go
--删除会员重复的会员数据,并且保留ID最小的会员,即取重复中的其中一条
delete from t_rm_vip_info where id in(select id from sccf)
go
--最后删除ID列
alter table t_rm_vip_info drop column id
go
--最后检查是不是还有重复的卡号,没有数据就是对的
select card_id from t_rm_vip_info group by card_id having COUNT(card_id)>1
--删除的ID在此表记录。可以查询对比
select * from sccf order by card_id
select * from t_rm_vip_info
where card_id in(select card_id from sccf) order by card_id