select min(b.fdate) date1, max(b.fdate) date2 from (select a.*, (fdate - rownum * (1/144)) cc,rownum from (select fdate from 1_test order by fdate) a) b group by cc如果时间规范的话,这个是按间隔10分钟来的,秒不规范的话可以to_char,to_date转换下,也不知道你要怎么样的、、
rownum * (1/144)????????????????
写了个数字类型的(相隔1000的分为一组,并打印最大值和最小值),需要你自己去更改,大概思路可以借鉴:WITH test AS(SELECT 141047 ID FROM dual UNION ALL SELECT 142047 ID FROM dual UNION ALL SELECT 143047 ID FROM dual UNION ALL SELECT 144047 ID FROM dual UNION ALL SELECT 153047 ID FROM dual UNION ALL SELECT 154047 ID FROM dual UNION ALL SELECT 155047 ID FROM dual) SELECT T2.ID STARTTIME, DECODE(SUBSTR(T2.C1, 1, INSTR(T2.C1, ' ')), NULL, T2.ID, SUBSTR(T2.C1, 1, INSTR(T2.C1, ' '))) ENDTIME FROM (SELECT T1.ID, MAX(T1.C1) C1 FROM (SELECT T.*, LTRIM(SYS_CONNECT_BY_PATH(T.ID, ' '), ' ') C1, CONNECT_BY_ISLEAF RN FROM (SELECT T.ID, DECODE(LAG(T.ID) OVER(ORDER BY T.ID) + 1000, T.ID, T.ID - 1000, NULL) PID FROM TEST T ORDER BY T.ID) T CONNECT BY T.ID = PRIOR T.PID) T1 WHERE T1.RN = 1 GROUP BY T1.ID) T2 ORDER BY T2.ID;
如果你时间规范的话(是00秒)to_date()就行了,否则那我只能说:select to_date(to_char(to_date('2013/09/09 13:00:13','yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi'),'yyyy/mm/dd hh24:mi') from dual、、
查询后结果
2013-8-8 14:10:47
2013-8-8 14:20:47
2013-8-8 14:30:47
2013-8-8 14:40:47
2013-8-8 15:30:47
2013-8-8 15:40:47
2013-8-8 15:50:47
2013-8-8 16:00:47
2013-8-8 16:10:47这个是一组 时间间隔是连续的
2013-8-8 14:10:47
2013-8-8 14:20:47
2013-8-8 14:30:47
2013-8-8 14:40:47这个是一组 时间间隔是连续的
2013-8-8 15:30:47
2013-8-8 15:40:47
2013-8-8 15:50:47
2013-8-8 16:00:47
2013-8-8 16:10:47是这样:sql 查询出来结果,这样就可以了date1 date2
2013-8-8 14:10:47 2013-8-8 14:40:47
2013-8-8 15:30:47 2013-8-8 16:10:47
把时间连续的分成一组,是Oracle数据库 应该要用row_number() 函数剩下的我会在后台解决!!
谢谢!
from (select a.*, (fdate - rownum * (1/144)) cc,rownum
from (select fdate from 1_test order by fdate) a) b
group by cc如果时间规范的话,这个是按间隔10分钟来的,秒不规范的话可以to_char,to_date转换下,也不知道你要怎么样的、、
SELECT 142047 ID FROM dual UNION ALL
SELECT 143047 ID FROM dual UNION ALL
SELECT 144047 ID FROM dual UNION ALL
SELECT 153047 ID FROM dual UNION ALL
SELECT 154047 ID FROM dual UNION ALL
SELECT 155047 ID FROM dual)
SELECT T2.ID STARTTIME,
DECODE(SUBSTR(T2.C1, 1, INSTR(T2.C1, ' ')),
NULL,
T2.ID,
SUBSTR(T2.C1, 1, INSTR(T2.C1, ' '))) ENDTIME
FROM (SELECT T1.ID, MAX(T1.C1) C1
FROM (SELECT T.*,
LTRIM(SYS_CONNECT_BY_PATH(T.ID, ' '), ' ') C1,
CONNECT_BY_ISLEAF RN
FROM (SELECT T.ID,
DECODE(LAG(T.ID) OVER(ORDER BY T.ID) + 1000,
T.ID,
T.ID - 1000,
NULL) PID
FROM TEST T
ORDER BY T.ID) T
CONNECT BY T.ID = PRIOR T.PID) T1
WHERE T1.RN = 1
GROUP BY T1.ID) T2
ORDER BY T2.ID;
如果你时间规范的话(是00秒)to_date()就行了,否则那我只能说:select to_date(to_char(to_date('2013/09/09 13:00:13','yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi'),'yyyy/mm/dd hh24:mi') from dual、、