日期:2014-05-16 浏览次数:20782 次
--S3为表名 SELECT * FROM S3 T1 WHERE T1.B > = ALL (SELECT T2.B FROM S3 T2 WHERE T2.A = T1.A) AND T1.C >= ALL (SELECT T3.C FROM S3 T3 WHERE T3.A = T1.A AND T3.B = T1.B)
------解决方案--------------------
select * from (
select a,b,c ,row_number()over(partition by a order by b desc,c desc) num1 from xxxx
) where num1=1
------解决方案--------------------
WITH t AS( SELECT 1 a,1 b,3 c FROM dual UNION SELECT 1 ,2 ,1 FROM dual UNION SELECT 1,2 ,2 FROM dual UNION SELECT 2 ,1 ,1 FROM dual UNION SELECT 2 ,2 ,1 FROM dual ) SELECT DISTINCT a ,Last_Value(b) over (PARTITION BY a ORDER BY b rows between unbounded preceding and unbounded following) b ,Last_Value(c) over (PARTITION BY a ORDER BY b,c rows between unbounded preceding and unbounded following) c FROM t;