ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 大家帮着看看,表中重复字段查询显示问题?

大家帮着看看,表中重复字段查询显示问题?

原创 Linux操作系统 作者:阿日 时间:2008-04-09 09:02:22 0 删除 编辑
我有一个这样的表:
create table per_all_people_f(person_id number,last_name varchar2(20),effective_end_date date)

insert into per_all_people_f values(1,'张三',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(2,'李四',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(3,'王二',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(4,'张三',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(5,'刘五',to_date('2009-10-01','yyyy-mm-dd'));
insert into per_all_people_f values(6,'王二',to_date('2009-10-01','yyyy-mm-dd'));
因为last_name字段有重复的,我想查询显示出唯一last_name的数据,结果显示成这样:
1        2        李四        2009-10-1
2        4        张三        2009-10-1
3        5        刘五        2009-10-1
4        6        王二        2009-10-1

我的处理办法是:
select max(person_id) as person_id,last_name,max(effective_end_date) as effective_end_date
from per_all_people_f
where last_name in (
select last_name
from (
select person_id, last_name,max(effective_end_date) as effective_end_date
from   per_all_people_f  group by person_id,last_name
)
group by last_name
having count(*)=1)
group by last_name
union
select max(person_id) as person_id,last_name,max(effective_end_date) as effective_end_date
from per_all_people_f
where last_name in (
select last_name
from (
select person_id, last_name,max(effective_end_date) as effective_end_date
from   per_all_people_f  group by person_id,last_name
)
group by last_name
having count(*)>1)
group by last_name

这样来写的,但不知道还有别的方法吗,谢谢!

经过朋友key_feng是这样解决的:
select person_id, last_name, effective_end_date from (
select p.*, row_number() over (partition by last_name order by person_id desc) RECNO
from
per_all_people_f p
) where RECNO = 1

看来我的SQL语句还仅局限于简单的水平中,有待提高。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/67668/viewspace-231278/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 学习oracle的理解
请登录后发表评论 登录
全部评论

注册时间:2007-12-26

  • 博文量
    35
  • 访问量
    141249