select a1,a2,a3,a4,a5 from a ta1 where not exists(select 1 from a ta2 where ta1.a1=ta2.a1 and ta1.a2=ta2.a2 and ta1.a3=ta2.a3 and ta1.a4=ta2.a4 and ta1.a5<ta2.a5)
select a1,a2,a3,a4,max(a5) from table group by a1,a2,a3,a4;
恩 多种方案 select a1,a2,a3,a4,a5 from (select a1,a2,a3,a4,a5,row_number() over(partition by a1,a2,a3,a4 order by a1,a2,a3,a4,A5 desc) rm from t) where rm<2
呵呵,速度都很快嘛?不过用3楼的方法 select a1,a2,a3,a4,a5 from (select a1,a2,a3,a4,a5,row_number() over(partition by a1,a2,a3,a4 order by a1,a2,a3,a4,A5 desc) rm from t) where rm = 1效率好点
select a1,a2,a3,a4,a5
from a ta1
where not exists(select 1 from a ta2
where ta1.a1=ta2.a1
and ta1.a2=ta2.a2
and ta1.a3=ta2.a3
and ta1.a4=ta2.a4
and ta1.a5<ta2.a5)
group by a1,a2,a3,a4;
from (select a1,a2,a3,a4,a5,row_number() over(partition by a1,a2,a3,a4 order by a1,a2,a3,a4,A5 desc) rm from t)
where rm<2
from (select a1,a2,a3,a4,a5,row_number() over(partition by a1,a2,a3,a4 order by a1,a2,a3,a4,A5 desc) rm from t)
where rm = 1效率好点