November 19 2015

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

WeChat_1447903184

 

其实这个问题可以简化为用开窗函数把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;

 

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



Copyright 2019. All rights reserved.

Posted 2015年11月19日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *