oracle 列 替换 最大值,oracle排序取不同列的最大值问题

其实这个问题可以简化为用开窗函数把6 5 4 3 排序 然后 rownum出来为1的获取出来就可以了

create table t(col1 number,col2 number,col3 number,col4 number,col5 number,col6 number);

insert into t values(1,2,3,4,5,6);

insert into t values(2,2,3,4,5,5);

insert into t values(2,2,3,4,4,5);

insert into t values(3,2,3,4,5,6);

insert into t values(3,2,3,3,5,6);

insert into t values(3,2,3,2,5,6);

insert into t values(4,2,3,4,5,6);

insert into t values(4,2,2,4,5,6);

insert into t values(4,2,1,4,5,6);

insert into t values(5,2,3,4,5,6);

insert into t values(5,2,3,4,5,6);

commit;

with tt as(

select t.*,row_number() over(partition by col1 order by col6 DESC,col5 desc,col4 desc,col3 desc) r from t

)

SELECT col1,col2,col3,col4,col5,col6 from tt where r=1;

这种问题虽然很简单 但是重点还在思路上