有表A,表A里数据如下
id date card accno ff_date
1 20110401 440301196804014128 019570200012501 2010-4-26
2 20110401 440301196804014128 019570200012401 2010-5-7
3 20110401 522601196610040828 159570200002001 2010-5-14
4 20110401 522601196610040828 159570200002001 2010-6-05
5 20110401 522601196610040828 159570200002001 2010-6-28
6 20110401 522601196610040828 159570200002001 2010-7-14
7 20110401 888888888888888888 555555555555555 2010-5-10我要从表A里取出每个accno对应最小日期的一条记录,即我查询后应该得到如下结果:
id date card accno ff_date
1 20110401 440301196804014128 019570200012501 2010-4-26
3 20110401 522601196610040828 159570200002001 2010-5-14
7 20110401 888888888888888888 555555555555555 2010-5-10请问SQL查询语句怎么写~~
id date card accno ff_date
1 20110401 440301196804014128 019570200012501 2010-4-26
2 20110401 440301196804014128 019570200012401 2010-5-7
3 20110401 522601196610040828 159570200002001 2010-5-14
4 20110401 522601196610040828 159570200002001 2010-6-05
5 20110401 522601196610040828 159570200002001 2010-6-28
6 20110401 522601196610040828 159570200002001 2010-7-14
7 20110401 888888888888888888 555555555555555 2010-5-10我要从表A里取出每个accno对应最小日期的一条记录,即我查询后应该得到如下结果:
id date card accno ff_date
1 20110401 440301196804014128 019570200012501 2010-4-26
3 20110401 522601196610040828 159570200002001 2010-5-14
7 20110401 888888888888888888 555555555555555 2010-5-10请问SQL查询语句怎么写~~
解决方案 »
- Oracle 9.2在WINDOW下导出的DMP文件在CentOS5.1平台上导入Oracle 10.2中时有警告,在导入过程中该注意些什么?sqlplus连接不上10g的
- oracle数据库如何备份还原为成sql server
- 程序包或函数f_getcblrsl处于无效状态!
- 问个SQL语句 该如何写
- oracle多表查询问题
- oracle语句的问题,求大神帮忙看看
- 菜鸟请教个各位大神!!急!!
- 求助大家一个关于重复数据删除的sql文!
- 我想用存储过程将查询结果email到指定邮箱。怎么实现?
- ORA-12154: TNS:could not resolve the connect identifier specified问题求解
- 超难sql语句,有人会写吗!
- oracle取流水号,如何解决并发?老问题了,但搜索了半天,没有找到一个好的解决方案。
from
(
select a.*
,row_number() over(partition by a.accno order by a.ff_date) rn
from a
)t
where rn=1
;
[SYS@orcl] SQL>with t1 as(
2 select 1 id, '20110401' date1, '440301196804014128' card, '019570200012501' acno,'2010-4-26' ff_Date from dual union all
3 select 2 id, '20110401' date1, '440301196804014128' card, '019570200012401' acno,'2010-5-7' ff_Date from dual union all
4 select 3 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-5-14' ff_Date from dual union all
5 select 4 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-05' ff_Date from dual union all
6 select 5 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-28' ff_Date from dual union all
7 select 6 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-7-14' ff_Date from dual union all
8 select 7 id, '20110401' date1, '888888888888888888' card, '555555555555555' acno,'2010-5-10' ff_Date from dual
9 )select min(id)keep(dense_rank first order by ff_Date) id,
10 min(date1)keep(dense_rank first order by ff_Date) date1,
11 card,
12 min(acno)keep(dense_rank first order by ff_Date) acno,
13 min(ff_Date)keep(dense_rank first order by ff_Date) id
14 from t1
15 group by card; ID DATE1 CARD ACNO ID
---------- -------- ------------------ --------------- ---------
1 20110401 440301196804014128 019570200012501 2010-4-26
3 20110401 522601196610040828 159570200002001 2010-5-14
7 20110401 888888888888888888 555555555555555 2010-5-10
[SYS@orcl] SQL>with a as(
2 select 1 id, '20110401' date1, '440301196804014128' card, '019570200012501' acno,'2010-4-26' ff_Date from dual union all
3 select 2 id, '20110401' date1, '440301196804014128' card, '019570200012401' acno,'2010-5-7' ff_Date from dual union all
4 select 3 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-5-14' ff_Date from dual union all
5 select 4 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-05' ff_Date from dual union all
6 select 5 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-28' ff_Date from dual union all
7 select 6 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-7-14' ff_Date from dual union all
8 select 7 id, '20110401' date1, '888888888888888888' card, '555555555555555' acno,'2010-5-10' ff_Date from dual
9 )select t.*
10 from
11 (
12 select a.*
13 ,row_number() over(partition by a.card order by a.ff_date) rn
14 from a
15 )t
16 where rn=1
17 ; ID DATE1 CARD ACNO FF_DATE RN
---------- -------- ------------------ --------------- --------- ----------
1 20110401 440301196804014128 019570200012501 2010-4-26 1
3 20110401 522601196610040828 159570200002001 2010-5-14 1
7 20110401 888888888888888888 555555555555555 2010-5-10 1
from
(select a.*,row_number() over(partition by a.card order by a.ff_date)
rn from a)t
where rn=1;