列名: a b c d e
2000-03 3 cc 2012/3/26 14:23:48 0
2012-02 2 qq 2012/3/26 20:55:50 0
2012-02 2 qq 2012/3/26 20:56:50 0
2012-02 2 qq 2012/3/26 20:57:50 0对于“2012-02”这三行记录,只留下最新的一条,语句怎么写?
2000-03 3 cc 2012/3/26 14:23:48 0
2012-02 2 qq 2012/3/26 20:55:50 0
2012-02 2 qq 2012/3/26 20:56:50 0
2012-02 2 qq 2012/3/26 20:57:50 0对于“2012-02”这三行记录,只留下最新的一条,语句怎么写?
(
a VARCHAR2(20),
b NUMBER(4),
c VARCHAR2(20),
d DATE,
e NUMBER(4)
);
INSERT INTO T171 VALUES('2000-03', 3, 'cc',
to_date('2012/03/26 14:23:48', 'YYYY/MM/DD HH24:MI:SS'), 0);
INSERT INTO T171 VALUES('2012-02', 3, 'qq',
to_date('2012/03/26 20:55:50', 'YYYY/MM/DD HH24:MI:SS'), 0);
INSERT INTO T171 VALUES('2012-02', 3, 'qq',
to_date('2012/03/26 20:56:50', 'YYYY/MM/DD HH24:MI:SS'), 0);
INSERT INTO T171 VALUES('2012-02', 3, 'qq',
to_date('2012/03/26 20:57:50', 'YYYY/MM/DD HH24:MI:SS'), 0);
测试结果:
如果这三条数据,b,c,e的值不同就挂了啊
select a,b,c,d,e from (
select a,b,c,d,e,row_number()over(partition by a order by d desc) rn from table
) where rn=1
select a,b,c,d,e,row_number()over(partition by a order by d desc) rn from T171
) where rn = 1
with t as(
select '2000-03' a,3 b,'cc' c,to_date('2012/3/26 14:23:48','yyyy/mm/dd hh24:mi:ss') d,0 e from dual
union all
select '2000-02' a,2,'qq',to_date('2012/3/26 20:55:50','yyyy/mm/dd hh24:mi:ss'),0 from dual
union all
select '2000-02' a,2,'qq',to_date('2012/3/26 20:56:50','yyyy/mm/dd hh24:mi:ss'),0 from dual
union all
select '2000-02' a,2,'qq',to_date('2012/3/26 20:57:50','yyyy/mm/dd hh24:mi:ss'),0 from dual
)select a,b,c,d,e
from t t1
where not exists
(select 1 from t t2 where t1.a=t2.a and t1.d<t2.d);A B C D E
------- ---------- -- ----------- ----------
2000-03 3 cc 2012-3-26 14:23:48 0
2000-02 2 qq 2012-3-26 20:57:50 0