with tab as ( select 'a' name, 'b' code1, 'e' code2, 7 num from dual union all select 'a','b','d',3 from dual union all select 'a','c','c',4 from dual union all select 'a','c','f',2 from dual union all select 'b','d','f',3 from dual union all select 'b','d','e',5 from dual) select name, code1,max(code2),max(num) from tab group by name,code1 order by name,code1 ----------------------------------- name code1 max(code2) max(num) a b e 7 a c f 4 b d f 5
with tab as( select 'a' name, 't' code1, 't' code2, 3 num from dual union all select 'a','b','e',7 from dual union all select 'a','b','d',3 from dual union all select 'a','n','s',4 from dual union all select 'a','c','c',4 from dual union all select 'a','c','f',7 from dual union all select 'b','d','f',3 from dual union all select 'b','d','e',5 from dual ) select name, code1, code2, num from( select name, code1, code2, num,row_number()over(partition by name,code1 order by name,code1, num desc) rn from tab ) where rn=1 order by name, code1NAME CODE1 CODE2 NUM ---------------------------- a b e 7 a c f 7 a n s 4 a t t 3 b d e 5
1 with tb as ( 2 select 'a' name, 't' code1, 't' code2, 3 num from dual 3 union all 4 select 'a','b','e',7 from dual 5 union all 6 select 'a','b','d',3 from dual 7 union all 8 select 'a','n','s',4 from dual 9 union all 10 select 'a','c','c',4 from dual 11 union all 12 select 'a','c','f',7 from dual 13 union all 14 select 'b','b','f',3 from dual 15 union all 16 select 'b','b','e',5 from dual 17 ) 18 select * from tb t where not exists ( 19* select * from tb where t.name=name and t.code1=code1 and t.num<num) SQL> /N C C NUM - - - ---------- a c f 7 b b e 5 a t t 3 a b e 7 a n s 4
你说的跟显示结果不太一样啊,第二列没有按code1排序,with data_tabname as ( select 'a' name, 't' code1, 't' code2, 3 num from dual union all select 'a','b','e',7 from dual union all select 'a','b','d',3 from dual union all select 'a','n','s',4 FROM dual union all select 'a','c','c',4 from dual union all select 'a','c','f',7 from dual union all select 'b','b','f',3 from dual union all select 'b','b','e',5 from dual )select a.name,a.code1,a.code2,b.maxnum FROM data_tabname a,(select name,code1,max(num) maxnum FROM data_tabname group by name,code1) b where a.name = b.name and a.code1 = b.code1 and a.num = b.maxnum order by a.name,a.code1;data_tabname就是你的数据表。
--我晕!你的帖子编辑的挺快啊,我刚看的时候可不是这个样子呢? --再一看,吓我一跳,变了这么多~~~ --改成分析函数就行了,用max的话就是忽略code2,嘿嘿~ with tab as ( select 'a' name, 't' code1, 't' code2, 3 num from dual union all select 'a' name, 'b' code1, 'e' code2, 7 num from dual union all select 'a','b','d',3 from dual union all select 'a','n','s',4 from dual union all select 'a','c','c',4 from dual union all select 'a','c','f',7 from dual union all select 'b','b','f',3 from dual union all select 'b','b','e',5 from dual)select name,code1,code2,num from ( select name,code1,code2,num, row_number() over(partition by name,code1 order by name, code1,num desc) row_num from tab ) where row_num <=1 ------------------------------- name code1 code2 num a b e 7 a c f 7 a n s 4 a t t 3 b b e 5
(
select 'a' name, 'b' code1, 'e' code2, 7 num from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',2 from dual
union all
select 'b','d','f',3 from dual
union all
select 'b','d','e',5 from dual)
select name, code1,max(code2),max(num) from tab group by name,code1 order by name,code1
-----------------------------------
name code1 max(code2) max(num)
a b e 7
a c f 4
b d f 5
select 'a' name, 't' code1, 't' code2, 3 num from dual
union all
select 'a','b','e',7 from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','n','s',4 from dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',7 from dual
union all
select 'b','d','f',3 from dual
union all
select 'b','d','e',5 from dual
)
select name, code1, code2, num from(
select name, code1, code2, num,row_number()over(partition by name,code1 order by name,code1, num desc) rn
from tab
)
where rn=1
order by name, code1NAME CODE1 CODE2 NUM
----------------------------
a b e 7
a c f 7
a n s 4
a t t 3
b d e 5
1 with tb as (
2 select 'a' name, 't' code1, 't' code2, 3 num from dual
3 union all
4 select 'a','b','e',7 from dual
5 union all
6 select 'a','b','d',3 from dual
7 union all
8 select 'a','n','s',4 from dual
9 union all
10 select 'a','c','c',4 from dual
11 union all
12 select 'a','c','f',7 from dual
13 union all
14 select 'b','b','f',3 from dual
15 union all
16 select 'b','b','e',5 from dual
17 )
18 select * from tb t where not exists (
19* select * from tb where t.name=name and t.code1=code1 and t.num<num)
SQL> /N C C NUM
- - - ----------
a c f 7
b b e 5
a t t 3
a b e 7
a n s 4
(
select 'a' name, 't' code1, 't' code2, 3 num from dual
union all
select 'a','b','e',7 from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','n','s',4 FROM dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',7 from dual
union all
select 'b','b','f',3 from dual
union all
select 'b','b','e',5 from dual
)select a.name,a.code1,a.code2,b.maxnum FROM data_tabname a,(select name,code1,max(num) maxnum FROM data_tabname
group by name,code1) b
where a.name = b.name and a.code1 = b.code1 and a.num = b.maxnum
order by a.name,a.code1;data_tabname就是你的数据表。
--再一看,吓我一跳,变了这么多~~~
--改成分析函数就行了,用max的话就是忽略code2,嘿嘿~
with tab as
(
select 'a' name, 't' code1, 't' code2, 3 num from dual
union all
select 'a' name, 'b' code1, 'e' code2, 7 num from dual
union all
select 'a','b','d',3 from dual
union all
select 'a','n','s',4 from dual
union all
select 'a','c','c',4 from dual
union all
select 'a','c','f',7 from dual
union all
select 'b','b','f',3 from dual
union all
select 'b','b','e',5 from dual)select name,code1,code2,num from (
select name,code1,code2,num, row_number() over(partition by name,code1 order by name, code1,num desc) row_num from tab
) where row_num <=1
-------------------------------
name code1 code2 num
a b e 7
a c f 7
a n s 4
a t t 3
b b e 5