Question

SQL 如何根据单个字段删除重复行并只保留重复行的最后一行

表
documents version1 version2
10050010-EGA-520 01 02
10050010-EGA-520 01 02
10050010-EGA-520 02 02
10050010-EGA-522 01 01
10415066-ELH-000 01 01
10415066-ELH-000 01 02
10415066-ELH-000 02 01
10415066-ELH-000 02 02
10415066-ELH-C20 01 01
10415066-ELH-C20 01 01
10415066-EPH-000 01 01
10415066-EPH-000 01 02
10415066-EPH-000 02 01
10415066-EPH-000 02 02
10415066-ETM-000 01 01
10415066-ETM-000 01 02
10415066-ETM-000 02 01
10415066-ETM-000 02 02
10415066-ETM-210 01 01
4791351-ELH-100 04 04
4792193-EGA-000 40 41
4792193-EGA-030 39 39
4792193-EGA-030 39 39
我要得到
10050010-EGA-520 02 02
10050010-EGA-522 01 01
10415066-ELH-000 02 02
10415066-ELH-C20 01 01
10415066-EPH-000 02 02
10415066-ETM-000 02 02
10415066-ETM-210 01 01
4791351-ELH-100 04 04
4792193-EGA-000 40 41
4792193-EGA-030 39 39

Answer 1

Oracle 写法：

create table t_test(
documents varchar2(400),
version1 varchar2(50),
version2 varchar2(50)
)
insert into t_test values('10050010-EGA-520','01','02');
insert into t_test values('10050010-EGA-520','02','02');
insert into t_test values('10050010-EGA-522','02','02');
insert into t_test values('10415066-ELH-000','01','01');
insert into t_test values('10415066-ELH-000','01','02');
insert into t_test values('10415066-ELH-000','02','02');
insert into t_test values('4792193-EGA','39','39');
insert into t_test values('4792193-EGA','39','39');
commit;
select documents,
version1,
version2
from ( select documents ,version1,version2,
row_number()over(partition by documents order by version1 desc ,version2 desc ) rw
from t_test
) a
where a.rw=1

Answer 2

select * from table

declare @id ,@max
declare cur cursor local
for
select documents , count(1) from table group by documents having count(1)>1
open cur
fetch cur into @id ,@max
while @@fetch_status=0
begin
set @max=@max-1
set rowcount @max
delete from table where documents =@id
fetch cur into @id,@max
end
set rowcount 0
close cur
dealloacte cur

select * from table

Answer 3

select * from table as a where exists
(select 1 from table where documents=a.documents group by documents
having max(version1)=a.version1 and max(version2)=a.version2)