有表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查询语句怎么写~~
解决方案 »
- 请教:数据库字符集由WE8ISO8859P1改为ZHS16GBK(在线等待)
- 求助!那位大侠能帮我优化一下这条语句
- sqlldr导入数据库乱码问题!
- 在PL/SQL developer工具中执行SQL语句时发生ORA-03113的错误,请看trace文件内容
- 数据库取数据放到excel中问题
- 关于blob列的全文检索问题
- oracle的***.dmp数据文件imp导入不成功,都有哪些原因?大家谈谈,谢谢!
- 请教commit难道不相当于“执行(F8)”吗?
- 在sql中求年龄的算法
- 请高手赐教!
- 超难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;