如何删除重复的行并保留具有更高值的行? [英] How do I delete duplicate rows and keep the row with higher value?

问题描述

我有一个包含 4 个关键字段的表格.A1(主键)、A2、A3、A4.

I have a table with 4 key fields. A1(primary key), A2, A3, A4.

我想删除 A3 中 A4 较低的重复项.

I'd like to remove duplicate in A3 where A4 is lower.

例如.A3 值 G258 - G258A4 值 1000 - 1500

Ex. A3 Values G258 - G258 A4 Values 1000 - 1500

删除 A3 = G258 且 A4 为 1000(下)的记录

Remove the record where A3 = G258 and A4 is 1000 (lower)

我该怎么办?

推荐答案

delete t1 
from some_table t1
join some_table t2
  on  t2.a3 = t1.a3
  and t2.a4 > t1.a4;

演示:http://rextester.com/RERGD32491

这将只保留每个 A3 中 A4 中具有最高值的行.A3 中具有相同值的两行可能在 A4 中具有相同的最高值.由于您没有指定在这种情况下要做什么 - 将保留两行.

This will only keep rows with the highest value in A4 per A3. It's possible that two rows with the same value in A3 have the same highest value in A4. Since you didn't specify what to do in that case - Both rows will be kept.

这篇关于如何删除重复的行并保留具有更高值的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！