表结构
字段1 字段2 字段3 数量1 数量2 数量3 字段4
a1 a2 a3 0 2 5 CC
a1 a2 a3 4 3 3 CI
b1 b2 b3 2 5 7 OP
b1 b2 b3 6 8 6 CL
。 查询最高状态的查询结果(状态由高到底:CC、CL、CI、OP)
字段1 字段2 字段3 数量 字段4
a1 a2 a3 7 CC
b1 b2 b3 20 CL
字段1 字段2 字段3 数量1 数量2 数量3 字段4
a1 a2 a3 0 2 5 CC
a1 a2 a3 4 3 3 CI
b1 b2 b3 2 5 7 OP
b1 b2 b3 6 8 6 CL
。 查询最高状态的查询结果(状态由高到底:CC、CL、CI、OP)
字段1 字段2 字段3 数量 字段4
a1 a2 a3 7 CC
b1 b2 b3 20 CL
(状态由高到底:CC、CL、CI、OP)只有这一个规则吗?
(select table.*,rownum()over(order by decode(字段4,OP,1,CI,2,CL,3,CC,4,0) desc) rn from table)
where rn=1;
ORDER BY DECODE(字段4,CC,1,CL,2,CI,3,OP,4,5)
b1 b2 b3的最高状态下数量1、2、3的和是多少:状态OP=14,CL=20,最终结果取CL的那条
(select table.*,rownum()over(order by decode(字段4,OP,1,CI,2,CL,3,CC,4,0) desc) rn from table) a
where rn=1;
(SELECT 字段1,字段2,字段3,数量1+数量2+数量3 as 数量,字段4
, ROW_NUMBER() OVER (PARTITION BY 字段1,字段2,字段3 ORDER BY DECODE(字段4,CC,1,CL,2,CI,3,OP,4,5),数量1+数量2+数量3 desc) AS rn
FROM mytab)
where rn=1
(SELECT strt,dest,flight,c_f+c_c+c_y as numbers,timeflag
, ROW_NUMBER() OVER (PARTITION BY strt,dest,flight ORDER BY DECODE(timeflag,'CC',1,'CL',2,'CI',3,'OP',4,5),c_f+c_c+c_y desc) AS rn
FROM fl_flight_info where flightdate = to_date('2009-05-20', 'YYYY-MM-DD'))
where rn=1
OK了,谢谢你们!