delete from tbl a where exists (select 1 from tbl where col1=a.col1 and col2=a.col2 and col3>a.col3);
或者delete a from tbl a left jon ( select col1, col2, max(col3) as m_col3 from tbl group by col1, col2) b on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.m_col3 where b.col1 is null
select a.col1,a.col2,a.col3 from tt a inner join tt b on a.col1=b.col1 and a.col2=b.col2 and a.col3<=b.col3 group by a.col1,a.col2,a.col3 having count(b.col1)=1
where exists (select 1 from tbl where col1=a.col1 and col2=a.col2 and col3>a.col3);
select col1, col2, max(col3) as m_col3 from tbl group by col1, col2) b on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.m_col3
where b.col1 is null
select a.col1,a.col2,a.col3
from tt a inner join tt b on a.col1=b.col1 and a.col2=b.col2 and a.col3<=b.col3
group by a.col1,a.col2,a.col3
having count(b.col1)=1
分组取最大值或最小值过滤即可