日期:2014-05-17  浏览次数:20891 次

请帮忙写一条SQL语句
现在在某一查询所得的结果集中有1字段的内容如num   vachar2(8),num字段有些行的值前三位是相同的,现在想保留这些相同结果中的第一行,请问这个SQL怎么实现?

------解决方案--------------------
分组,不过有要求,必须其他的选择字段同num一样(就是num重复,其他字段同样重复)
------解决方案--------------------
--这样行不
--不过我写得实在是太复杂了,应该有简单的方法吧
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