请教一个sql语句表如下id val1 val2 time
1 10 12 2009-12-01 03:04:23
2 10 12 2009-12-01 23:14:23
3 10 12 2009-12-03 03:24:23
4 10 12 2009-12-03 23:44:23
5 10 12 2009-12-04 03:54:23
6 10 12 2009-12-04 21:54:23
如何求出每天中最后时间的数据,即希望的结果
id val1 val2 time
2 10 12 2009-12-01 23:14:23
4 10 12 2009-12-03 23:44:23
6 10 12 2009-12-04 21:54:23
1 10 12 2009-12-01 03:04:23
2 10 12 2009-12-01 23:14:23
3 10 12 2009-12-03 03:24:23
4 10 12 2009-12-03 23:44:23
5 10 12 2009-12-04 03:54:23
6 10 12 2009-12-04 21:54:23
如何求出每天中最后时间的数据,即希望的结果
id val1 val2 time
2 10 12 2009-12-01 23:14:23
4 10 12 2009-12-03 23:44:23
6 10 12 2009-12-04 21:54:23
where time=(select max(time) from table group by to_char(time,yyyy-mm-dd));
没环境,不不知道行不。
select id,val1,val2,time from table
where time=any(select max(time) from table group by to_char(time,yyyy-mm-dd));
union
select 1 id, 10 val1,12 val2,to_date('2009-12-01 23:14:23','yyyy-mm-dd hh24:mi:ss' ) from dual
union
select 1 id, 10 val1,12 val2,to_date('2009-12-03 03:24:23','yyyy-mm-dd hh24:mi:ss') from dual
union
select 1 id, 10 val1,12 val2,to_date('2009-12-03 23:44:23','yyyy-mm-dd hh24:mi:ss') from dual
union
select 1 id, 10 val1,12 val2,to_date('2009-12-04 03:54:23 ','yyyy-mm-dd hh24:mi:ss') from dual
union
select 1 id, 10 val1,12 val2,to_date('2009-12-04 21:54:23','yyyy-mm-dd hh24:mi:ss') from dual)
select * from
(select id,val1,val2,time,row_number() over(partition by trunc(time) order by time desc) rn from tab) where rn =1
---------- ---------- ---------- -------------------
6 10 12 2009-12-04 21:54:23
5 10 12 2009-12-04 03:54:23
4 10 12 2009-12-03 23:44:23
3 10 12 2009-12-03 03:24:23
2 10 12 2009-12-01 23:14:23
1 10 12 2009-12-01 03:04:23已选择6行。SQL> select id,val1,val2,time from test
2 where time=any(select max(time) from test group by to_char(time,'yyyy-mm-dd
'))
3 ORDER BY 4; ID VAL1 VAL2 TIME
---------- ---------- ---------- -------------------
2 10 12 2009-12-01 23:14:23
4 10 12 2009-12-03 23:44:23
6 10 12 2009-12-04 21:54:23SQL>
-------------------
select * from
(
select id,val1,val2,time,
row_number() over(partition by trunc(time) order by time desc) r1
) t
where r1 = 1
(
select id,val1,val2,time,
row_number() over(partition by trunc(time) order by time desc) r1
from table_name
) t
where r1 = 1