表A结构及数据示例如下:NAME TIME1 TIME2
---------------------------------
TOM 2009-01-01 2009-01-02
JACK 2010-01-01 2010-02-03
TOM 2009-01-01 2009-07-02
TOM 2009-01-01 2009-12-02
TOM 2009-01-01 2009-02-05
JACK 2010-01-01 2010-02-10
BEN 2009-12-15 2010-02-03希望选出每个NAME值TIME2列最新的数据,即选出:
TOM 2009-01-01 2009-02-05
JACK 2010-01-01 2010-02-10
BEN 2009-12-15 2010-02-03多谢啦。
---------------------------------
TOM 2009-01-01 2009-01-02
JACK 2010-01-01 2010-02-03
TOM 2009-01-01 2009-07-02
TOM 2009-01-01 2009-12-02
TOM 2009-01-01 2009-02-05
JACK 2010-01-01 2010-02-10
BEN 2009-12-15 2010-02-03希望选出每个NAME值TIME2列最新的数据,即选出:
TOM 2009-01-01 2009-02-05
JACK 2010-01-01 2010-02-10
BEN 2009-12-15 2010-02-03多谢啦。
解决方案 »
- <--------请教一个很简单的查询---------->
- 一个关于select的小问题
- 晚间自动备份表的语句
- 能在ORACLE的存储过程中创建触发器吗?怎么做?
- 有没有pl/sql的代码美化工具?
- oracle数据库图片的存取
- 血本提问100分!!!!!
- 请问有谁在unix下装过oracle的。。。
- solr查询慢,测试solr只有30多条数据,查询时间到达3s-4s秒,求大佬5帮忙!
- 我的pl/sql登陆不上去?ora-12569:TNS:包检验和失败。请大神们指教!!!!
- 用pl/sql往oracle中插入sql数据怎么停止了那
- 无法加载 DLL“OraOps10.dll”: 找不到指定的程序。 (异常来自 HRESULT:0x8007007F)。错误
(select t.*, row_number() over(partition by name order by time2 desc) rn from t)
where rn = 1;
where not exists (
select 1 from t t2
where t1.name = t2.name and t1.time2 < t2.time2);
where (t1.name, t1.time2) in
(
select name, max(time2) from t group by name
);
select * from tb a
where not exists(select 1 from tb b
where a.name=b.name and a.time2<b.time2)
where a.name=b.name and a.time2=b.time2
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as csdn
SQL>
SQL> with tab as
2 (
3 select 'JACK' name,to_date('2010-01-01','yyyy-MM-dd') time1,to_date('2010-02-03','yyyy-MM-dd') time2 from dual union all
4 select 'TOM',to_date('2009-01-01','yyyy-MM-dd') ,to_date('2009-07-02','yyyy-MM-dd') from dual union all
5 select 'TOM',to_date('2009-01-01','yyyy-MM-dd'),to_date('2009-12-02','yyyy-MM-dd') from dual union all
6 select 'TOM',to_date('2009-01-01','yyyy-MM-dd'),to_date('2009-02-05','yyyy-MM-dd') from dual union all
7 select 'JACK',to_date('2010-01-01','yyyy-MM-dd'),to_date('2010-02-10','yyyy-MM-dd') from dual union all
8 select 'BEN',to_date('2009-12-15','yyyy-MM-dd'),to_date('2010-02-03','yyyy-MM-dd') from dual
9 )
10 select name, time1, time2
11 from (select name,
12 time1,
13 time2,
14 rank() over(partition by name order by time2 desc) rnk
15 from tab)
16 where rnk = 1
17 /NAME TIME1 TIME2
---- ----------- -----------
BEN 2009-12-15 2010-2-3
JACK 2010-1-1 2010-2-10
TOM 2009-1-1 2009-12-2SQ