測試如下:
SQL> select col1,col2,to_char(col3,'yyyy-mm-dd am hh:mi:ss') from test_10;
COL1 COL2 TO_CHAR(COL3,'YYYY-MM-DDAMHH:M
---------- ---------- ------------------------------
A 1 2007-01-29 上午 06:36:43
A 2 2007-01-30 下午 04:06:39
B 3 2007-01-29 上午 04:13:55
B 4 2007-01-28 下午 11:34:41
C 5 2007-01-30 下午 04:06:39
C 6 2007-01-29 上午 05:40:32
D 7 2007-01-29 上午 05:22:17
D 8 2007-01-28 下午 11:34:43
A 9 2007-01-30 下午 03:59:38
9 rows selected
SQL> select col1,col2,first_value(col3) over (partition by col1 order by col3) from test_10
2 where col3 >= to_date('2007/1/30 下午 04:00:39','yyyy-mm-dd am hh:mi:ss');
COL1 COL2 FIRST_VALUE(COL3)OVER(PARTITIO
---------- ---------- ------------------------------
A 2 2007/1/30 下午 04:06:39
C 5 2007/1/30 下午 04:06:39
[B]當再插入如下記錄,hanjs的方法就達不到要求了.[/B]
SQL> insert into test_10 values ('A','9',to_date('2007/1/30下午04:09:38','yyyy-mm-dd am hh:mi:ss'));
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> select col1,col2,first_value(col3) over (partition by col1 order by col3) from test_10
2 where col3 >= to_date('2007/1/30 下午 04:00:39','yyyy-mm-dd am hh:mi:ss');
COL1 COL2 FIRST_VALUE(COL3)OVER(PARTITIO
---------- ---------- ------------------------------
A 2 2007/1/30 下午 04:06:39
A 9 2007/1/30 下午 04:06:39
C 5 2007/1/30 下午 04:06:39
[B]用fangjinde兄的方法,通過.[/B]
SQL> select col1, col2, to_char(col3, 'yyyy-mm-dd hh24:mi:ss')
2 from (select col1,
3 col2,
4 col3,
5 rank() over(partition by col1 order by col3) rn
6 from test_10
7 where col3 >=
8 to_date('2007-01-30 04:00:39', 'yyyy-mm-dd hh24:mi:ss'))
9 where rn = 1
10 /
COL1 COL2 TO_CHAR(COL3,'YYYY-MM-DDHH24:M
---------- ---------- ------------------------------
A 9 2007-01-30 15:59:38
C 5 2007-01-30 16:06:39
SQL>
[B]多謝兩位![/B]