表结构与数据:
id name time
1 张三 2010-01-01 4:30:30
1 张三 2010-01-01 8:30:30
1 张三 2010-01-01 9:30:30
1 张三 2010-01-02 4:30:30
1 张三 2010-01-02 5:30:30
1 张三 2010-01-02 6:30:30
1 张三 2010-01-03 7:30:30
1 张三 2010-01-03 8:30:30
1 张三 2010-01-03 9:30:30
1 张三 2010-01-04 7:30:30
1 张三 2010-01-04 8:30:30
1 张三 2010-01-05 9:30:30求:每天最早的几条记录或每天最晚的几条记录 例如:我要获得每天最早的两条记录
结果:
1 张三 2010-01-01 4:30:30
1 张三 2010-01-01 8:30:30
1 张三 2010-01-02 4:30:30
1 张三 2010-01-02 5:30:30
1 张三 2010-01-03 7:30:30
1 张三 2010-01-03 8:30:30
1 张三 2010-01-04 7:30:30
1 张三 2010-01-04 8:30:30
1 张三 2010-01-05 9:30:30
如果是最早的一条记录
则结果:
1 张三 2010-01-01 4:30:30
1 张三 2010-01-02 4:30:30
1 张三 2010-01-03 7:30:30
1 张三 2010-01-04 7:30:30
1 张三 2010-01-05 9:30:30请高手指点。
id name time
1 张三 2010-01-01 4:30:30
1 张三 2010-01-01 8:30:30
1 张三 2010-01-01 9:30:30
1 张三 2010-01-02 4:30:30
1 张三 2010-01-02 5:30:30
1 张三 2010-01-02 6:30:30
1 张三 2010-01-03 7:30:30
1 张三 2010-01-03 8:30:30
1 张三 2010-01-03 9:30:30
1 张三 2010-01-04 7:30:30
1 张三 2010-01-04 8:30:30
1 张三 2010-01-05 9:30:30求:每天最早的几条记录或每天最晚的几条记录 例如:我要获得每天最早的两条记录
结果:
1 张三 2010-01-01 4:30:30
1 张三 2010-01-01 8:30:30
1 张三 2010-01-02 4:30:30
1 张三 2010-01-02 5:30:30
1 张三 2010-01-03 7:30:30
1 张三 2010-01-03 8:30:30
1 张三 2010-01-04 7:30:30
1 张三 2010-01-04 8:30:30
1 张三 2010-01-05 9:30:30
如果是最早的一条记录
则结果:
1 张三 2010-01-01 4:30:30
1 张三 2010-01-02 4:30:30
1 张三 2010-01-03 7:30:30
1 张三 2010-01-04 7:30:30
1 张三 2010-01-05 9:30:30请高手指点。
select id,name,time from t1 where rn<=2
--最早
select * from
(
select a.*,row_number()over(partition by name,trunc(time) order by time) as num
from tb a
)
where num=1;--最晚
select * from tb a
where not exists(select 1 from tb b
where a.name=b.name and trunc(a.time)=trunc(b.time) and a.time<b.time)
select * from
(select id,name,
row_number(partition by to_char(time,'yyyy-mm-dd') order by time) rn
from tablename)
where rn=1
--查询每天最早的一条记录 如果是最晚的话就改成order by time desc
select * from
(select id,name,
row_number(partition by id,name,to_char(time) order by time) rn
from tablename)
where rn=1
--也不一定对
ID NAME TO_CHAR(TIME,'YYYY-MM-DDHH24:M
----------- ---------- ------------------------------
1 张三 2010-01-01 04:30:30
1 张三 2010-01-01 08:30:30
1 张三 2010-01-01 09:30:30
1 张三 2010-01-02 04:30:30
1 张三 2010-01-02 05:30:30
1 张三 2010-01-02 06:30:30
1 张三 2010-01-03 07:30:30
1 张三 2010-01-03 08:30:30
1 张三 2010-01-03 09:30:30
1 张三 2010-01-04 07:30:30
1 张三 2010-01-04 08:30:30
1 张三 2010-01-05 09:30:30
12 rows selected
SQL>
SQL> with t1 as (select id ,name ,time,row_number()over(partition by id,trunc(time) order by time asc) rn from tb_1)
2 select id,name,to_char(time ,'yyyy-mm-dd hh24:mi:ss')from t1 where rn<=2;
ID NAME TO_CHAR(TIME,'YYYY-MM-DDHH24:M
----------- ---------- ------------------------------
1 张三 2010-01-01 04:30:30
1 张三 2010-01-01 08:30:30
1 张三 2010-01-02 04:30:30
1 张三 2010-01-02 05:30:30
1 张三 2010-01-03 07:30:30
1 张三 2010-01-03 08:30:30
1 张三 2010-01-04 07:30:30
1 张三 2010-01-04 08:30:30
1 张三 2010-01-05 09:30:30
9 rows selected
SQL>
2 select id,name,to_char(time ,'yyyy-mm-dd hh24:mi:ss')from t1 where rn<=1;
ID NAME TO_CHAR(TIME,'YYYY-MM-DDHH24:M
----------- ---------- ------------------------------
1 张三 2010-01-01 04:30:30
1 张三 2010-01-02 04:30:30
1 张三 2010-01-03 07:30:30
1 张三 2010-01-04 07:30:30
1 张三 2010-01-05 09:30:30
SQL>
SQL> edi
已写入 file afiedt.buf 1 with tb as
2 (select 1 id,'张三' name,to_date('2010-01-01 4:30:30','yyyy-mm-dd hh24:mi:ss') time from dual
3 union all
4 select 1,'张三',to_date('2010-01-01 8:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
5 select 1,'张三',to_date('2010-01-01 9:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
6 select 1,'张三',to_date('2010-01-02 4:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
7 select 1,'张三',to_date('2010-01-02 5:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
8 select 1,'张三',to_date('2010-01-02 6:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
9 select 1,'张三',to_date('2010-01-03 7:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
10 select 1,'张三',to_date('2010-01-03 8:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
11 select 1,'张三',to_date('2010-01-03 9:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
12 select 1,'张三',to_date('2010-01-04 7:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
13 select 1,'张三',to_date('2010-01-04 8:30:30','yyyy-mm-dd hh24:mi:ss') from dual union all
14 select 1,'张三',to_date('2010-01-05 9:30:30','yyyy-mm-dd hh24:mi:ss') from dual)
15 select id,name,to_char(time,'yyyy-mm-dd hh24:mi:ss') time
16 from tb a
17 where not exists(select 1 from tb b
18* where a.id=b.id and a.name=b.name and trunc(a.time)=trunc(b.time) and a.time>b.time)
SQL> / ID NAME TIME
---------- ---- -------------------
1 张三 2010-01-04 07:30:30
1 张三 2010-01-05 09:30:30
1 张三 2010-01-01 04:30:30
1 张三 2010-01-03 07:30:30
1 张三 2010-01-02 04:30:30已用时间: 00: 00: 00.06