表结构如下hyk1卡号h#1 卡内号nh#1 姓名hr1 积分rje110232 7897890010232 颜其军 8
10232 7897890102326 洪柳华 10
11475 7897890114756 王彬 3想要的结果卡号h#1 卡内号nh#1 姓名hr1 积分rje110232 7897890102326 洪柳华 10
11475 7897890114756 王彬 3根据卡号对比,找出重复值,保留积分高的,删除积分低的
10232 7897890102326 洪柳华 10
11475 7897890114756 王彬 3想要的结果卡号h#1 卡内号nh#1 姓名hr1 积分rje110232 7897890102326 洪柳华 10
11475 7897890114756 王彬 3根据卡号对比,找出重复值,保留积分高的,删除积分低的
where not exists(select 1 from hyk1 where [卡号h#1]=a.[卡号h#1] and 积分rje1>a.积分rje1)
insert into hyk1 select 10232,'7897890010232','颜其军',8
insert into hyk1 select 10232,'7897890102326','洪柳华',10
insert into hyk1 select 11475,'7897890114756','王彬',3
go
select * from hyk1 a
where not exists(select 1 from hyk1 where [卡号h#1]=a.[卡号h#1] and 积分rje1>a.积分rje1)
/*
卡号h#1 卡内号nh#1 姓名hr1 积分rje1
----------- -------------------- ---------- -----------
10232 7897890102326 洪柳华 10
11475 7897890114756 王彬 3(2 行受影响)*/
go
drop table hyk1
insert into hyk1 select 10232,'7897890010232','颜其军',8
insert into hyk1 select 10232,'7897890102326','洪柳华',10
insert into hyk1 select 11475,'7897890114756','王彬',3
go
delete t from hyk1 t where exists(select 1 from hyk1 where [卡号h#1]=t.[卡号h#1] and 积分rje1>t.积分rje1)select * from hyk1
/*
卡号h#1 卡内号nh#1 姓名hr1 积分rje1
----------- -------------------- ---------- -----------
10232 7897890102326 洪柳华 10
11475 7897890114756 王彬 3(2 行受影响)*/
go
drop table hyk1数据是晴天大大的