日期:2014-05-17 浏览次数:21094 次
select *
from table1 t1
where not exists (select 1
from table1 t2
where t2.name1 = t1.name1
and t2.zs > t1.zs) order by t1.name1;
NAME1 NAME2 ZS
----- ----- --
aa a2 5
bb b1 20
cc c2 30
cc c1 30
dd d 40
WITH table1 AS
(
SELECT 'aa' AS name1, 'a1' AS name2,'30' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a2' AS name2,'5' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a3' AS name2,'3' AS zs FROM dual
union all
SELECT 'aa' AS name1, 'a4' AS name2,'3' AS zs FROM dual
union all
SELECT 'bb' AS name1, 'b1' AS name2,'20' AS zs FROM dual
union all
SELECT 'bb' AS name1, 'b2' AS name2,'11' AS zs FROM dual
union all
SELECT 'cc' AS name1, 'c1' AS name2,'30' AS zs FROM dual
union all
SELECT 'cc' AS name1, 'c2' AS name2,'30' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd' AS name2,'40' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd1' AS name2,'25' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd2' AS name2,'20' AS zs FROM dual
union all
SELECT 'dd' AS name1, 'd3' AS name2,'0' AS zs FROM dual
)
select name1,name2,zs from (
select row_number()over(partition by name1 order by name2,zs desc) as rn ,t.* from table1 t) where rn = 1