--这样行不 --不过我写得实在是太复杂了,应该有简单的方法吧 select b.id,b.n1 from ( select newid,max(rid) as rid from ( select substr(id,1,3) as newid,rownum as rid,id,n1 from ( select '1112' as id,'aa' as n1 from dual union select '1113','bb' from dual union select '1114','bb' from dual union select '2113','bb' from dual union select '2114','bb' from dual ) ) group by newid ) a inner join (select substr(id,1,3) as newid,rownum as rid,id,n1 from ( select '1112' as id,'aa' as n1 from dual union select '1113','bb' from dual union select '1114','bb' from dual union select '2113','bb' from dual union select '2114','bb' from dual ) ) b on a.rid=b.rid
create table mtb5 (id varchar2(10),name varchar2(10),num varchar2(10))insert into mtb5 select '1','a','aaabdf' from dual union select '2','b','aaabdf' from dual union select '3','c','bbbbdf' from dual union select '4','d','cccbdf' from dual union select '5','e','cccbdf' from dual select id,name from( select id,name ,row_number()over(partition by substr(t.num,1,3) order by substr(t.num,1,3),rn) rn from( select t.*,rownum rn from mtb5 t ) t ) where rn=1
知道了,谢谢楼上几位大哥!row_number() over(partition by f1 order by f2)
--不过我写得实在是太复杂了,应该有简单的方法吧
select b.id,b.n1 from
(
select newid,max(rid) as rid
from
(
select substr(id,1,3) as newid,rownum as rid,id,n1 from
(
select '1112' as id,'aa' as n1 from dual
union
select '1113','bb' from dual
union
select '1114','bb' from dual
union
select '2113','bb' from dual
union
select '2114','bb' from dual
)
)
group by newid
) a
inner join
(select substr(id,1,3) as newid,rownum as rid,id,n1 from
(
select '1112' as id,'aa' as n1 from dual
union
select '1113','bb' from dual
union
select '1114','bb' from dual
union
select '2113','bb' from dual
union
select '2114','bb' from dual
)
) b
on a.rid=b.rid
select '1','a','aaabdf' from dual union
select '2','b','aaabdf' from dual union
select '3','c','bbbbdf' from dual union
select '4','d','cccbdf' from dual union
select '5','e','cccbdf' from dual select id,name
from(
select id,name
,row_number()over(partition by substr(t.num,1,3) order by substr(t.num,1,3),rn) rn
from(
select t.*,rownum rn
from mtb5 t
) t
)
where rn=1