SQL> select * from test1 2 /DATE1 NO Q ---------- --- ---------- 14-7月 -05 001 5 15-7月 -05 001 6 16-7月 -05 001 7 17-7月 -05 001 7 18-7月 -05 001 7 17-7月 -05 002 6 13-7月 -05 003 3 14-7月 -05 003 4 15-7月 -05 003 4 16-7月 -05 003 4 17-7月 -05 003 5 18-7月 -05 003 5已选择12行。已用时间: 00: 00: 00.00 SQL> select to_char(date1,'yyyy/mm/dd') "date",no,q from ( 2 select t.*,row_number() over(partition by no,q order by date1) rn, 3 dense_rank() over(partition by no order by q desc) dr 4 from test1 t 5 ) where rn = 1 and dr = 1 6 /date NO Q ---------- --- ---------- 2005/07/16 001 7 2005/07/17 002 6 2005/07/17 003 5已用时间: 00: 00: 00.00
如果最后更新的日期是最大那就 select a.* from test a, (select no,max(date)max_date from test group by no) b where a.no=b.no and a.date = b.max_date要不就 select b.* from test b, (select NO,max(rowid)seq from test group by NO)a where b.rowid = a.seq and b.NO= a.NO
select * from (select test.*,row_number() over(partition by no order by q desc,date asc) rnum from test) t where rnum=1;
2 /DATE1 NO Q
---------- --- ----------
14-7月 -05 001 5
15-7月 -05 001 6
16-7月 -05 001 7
17-7月 -05 001 7
18-7月 -05 001 7
17-7月 -05 002 6
13-7月 -05 003 3
14-7月 -05 003 4
15-7月 -05 003 4
16-7月 -05 003 4
17-7月 -05 003 5
18-7月 -05 003 5已选择12行。已用时间: 00: 00: 00.00
SQL> select to_char(date1,'yyyy/mm/dd') "date",no,q from (
2 select t.*,row_number() over(partition by no,q order by date1) rn,
3 dense_rank() over(partition by no order by q desc) dr
4 from test1 t
5 ) where rn = 1 and dr = 1
6 /date NO Q
---------- --- ----------
2005/07/16 001 7
2005/07/17 002 6
2005/07/17 003 5已用时间: 00: 00: 00.00
select a.* from test a,
(select no,max(date)max_date from test group by no) b
where a.no=b.no and a.date = b.max_date要不就
select b.* from test b,
(select NO,max(rowid)seq from test group by NO)a
where b.rowid = a.seq
and b.NO= a.NO
(select test.*,row_number() over(partition by no order by q desc,date asc) rnum from test) t where rnum=1;