删除重复数据,保留其中指定的数据.
请问
表1:结构如下
pkid skuid locid cntqty
1 123 c001 2
2 123 c001 3
3 234 c002 4
4 234 c002 4
5 123 c001 1

现在需要保留重复数据(skuid+locid相等即为重复)中cntqty 值较大的一个,如何操作呢?谢谢

[解决办法]
delete from tablename where not exists (select top 1 * from tablename group by locid,skuid order by cntqty desc,pkid desc)
[解决办法]
declare @ta table(pkid int, skuid int, locid varchar(4), cntqty int)
insert @ta
select 1, 123, 'c001 ', 2
union all select 2, 123, 'c001 ', 3
union all select 3, 234, 'c002 ', 4
union all select 4, 234, 'c002 ', 4
union all select 5, 123, 'c001 ', 1

delete a from @ta a where pkid not in
(select top 1 pkid from @ta where skuid=a.skuid and locid =a.locid order by cntqty desc )

select *from @ta

（所影响的行数为 5 行）


（所影响的行数为 3 行）

pkid skuid locid cntqty
----------- ----------- ----- -----------
2 123 c001 3
4 234 c002 4

（所影响的行数为 2 行）