时间查询SQL语句

Question

表名为juncperiod中的数据如下：
area    junc      time    period    datetime
0 1 1170 3 2011/7/17 15:36
0 1 750 3 2011/7/17 15:36
0 1 1350 3 2011/7/17 15:36
0 1 1440 3 2011/7/17 15:36
0 2 1430 3 2011/7/17 15:36
0 3 1440 2 2011/7/17 15:36
0 3 360 2 2011/7/17 15:36
0 3 1320 2 2011/7/17 15:36
0 4 1440 5 2011/7/17 15:36
0 4 360 5 2011/7/17 15:36
0 4 1320 5 2011/7/17 15:36
0 5 1440 4 2011/7/17 15:36
0 5 1200 4 2011/7/17 15:36
0 6 1440 3 2011/7/17 15:36
0 6 1320 3 2011/7/17 15:36
0 6 360 3 2011/7/17 15:36
0 7 1440 3 2011/7/17 15:36
0 7 360 3 2011/7/17 15:36
0 7 1320 3 2011/7/17 15:36
0 8 1440 4 2011/7/17 15:36
0 8 1320 4 2011/7/17 15:36
0 8 360 4 2011/7/17 15:36
0 9 1440 3 2011/7/17 15:36
0 9 750 3 2011/7/17 15:36
0 9 1170 3 2011/7/17 15:36
0 9 1350 3 2011/7/17 15:36
0 10 1440 2 2011/7/17 15:36
0 10 750 2 2011/7/17 15:36
0 10 1170 2 2011/7/17 15:36
0 10 1350 2 2011/7/17 15:36SQL想得到如下结果：
area    junc      time    stage    datetime
0 1 750 3 2011/7/17 15:36
0 2 1430 3 2011/7/17 15:36
0 3 360 2 2011/7/17 15:36
0 4 360 5 2011/7/17 15:36
0 5 1200 4 2011/7/17 15:36
0 6 360 3 2011/7/17 15:36
0 7 360 3 2011/7/17 15:36
0 8 360 4 2011/7/17 15:36
0 9 750 3 2011/7/17 15:36
0 10 750 2 2011/7/18 15:36本人自己写了一段查询语句，但结果不符：select distinct (t.junc),
                t.area,
                t.period,
                first_value(t.time) over(partition by t.junc order by t.time desc) time,
                t.stage,
                t.datetime
  from juncperiod t
 where t.area = 0
   and (select trunc(sysdate) + t.time / 60 / 24 from dual) <= sysdate
 order by datetime desc
其中time字段中的内容为从当天凌晨零点到当前的总分钟数。重点看junc编号为2和5的几行数据，比如junc编号为2的时，执行以上本人写的SQL时，查询不到行，因为1430（2011-07-18 23：50：00）比当前时间（假如当前时间为：201-07-18 13：30）靠后，所以我的意思是当查询不到比当前时间小的数据时，自动把大于当前时间的第一个数显示出来。junc编号为2的返回time为1430的行，junc编号为5的返回time为1200的行。
不知列位能否指点一二...
在线等

Answer 1

如：
当前时间为2011-07-18 14：00：00，做如下查询：select distinct (t.junc),
                t.area,
                t.period,
                first_value(t.time) over(partition by t.junc order by t.time desc) time,
                t.stage,
                t.datetime
  from juncperiod t
 where t.area = 0
   and (select trunc(sysdate) + t.time / 60 / 24 from dual) <= sysdate
 order by datetime desc结果为空。
以上语句的意思是，把当前时间与junc中的值转换为系统时间后的值进行比较，如果当前时间大于junc值转换后的时间时，列出结果；如果没有比当前时间小的，则列出大于当前时间的结果。大于当前时间的结果可能有多个，列出大于并最靠近当前时间的那一条记录。

Answer 2

[TEST@orcl] SQL>with juncperiod as(
  2  select 0 area, 1  junc, 1170 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  3  select 0 area, 1  junc, 750  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  4  select 0 area, 1  junc, 1350 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  5  select 0 area, 1  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  6  select 0 area, 2  junc, 1430 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  7  select 0 area, 3  junc, 1440 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  8  select 0 area, 3  junc, 360  time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  9  select 0 area, 3  junc, 1320 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 10  select 0 area, 4  junc, 1440 time,5 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 11  select 0 area, 4  junc, 360  time,5 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 12  select 0 area, 4  junc, 1320 time,5 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 13  select 0 area, 5  junc, 1440 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 14  select 0 area, 5  junc, 1200 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 15  select 0 area, 6  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 16  select 0 area, 6  junc, 1320 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 17  select 0 area, 6  junc, 360  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 18  select 0 area, 7  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 19  select 0 area, 7  junc, 360  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 20  select 0 area, 7  junc, 1320 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 21  select 0 area, 8  junc, 1440 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 22  select 0 area, 8  junc, 1320 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 23  select 0 area, 8  junc, 360  time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 24  select 0 area, 9  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 25  select 0 area, 9  junc, 750  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 26  select 0 area, 9  junc, 1170 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 27  select 0 area, 9  junc, 1350 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 28  select 0 area, 10 junc, 1440 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 29  select 0 area, 10 junc, 750  time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 30  select 0 area, 10 junc, 1170 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 31  select 0 area, 10 junc, 1350 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual
 32  ),t1 as(
 33   select case when trunc(sysdate) + t.time / 60 / 24 <= sysdate then row_number()over(partition by t.junc order by t.time DESC) else 0 E
ND row_1,
 34          case when trunc(sysdate) + t.time / 60 / 24 >  sysdate then row_number()over(partition by t.junc order by t.time DESC) else 0 E
ND row_2,
 35          t.*
 36     from juncperiod t
 37  ),t2 as(
 38   SELECT MAX(t1.row_1)over(partition by t1.junc) m_row_1,
 39          max(t1.row_2)over(partition by t1.junc) m_row_2,
 40          t1.*
 41     FROM t1
 42  )
 43   SELECT t2.junc,
 44          t2.area,
 45          MIN(t2.period)KEEP(dense_rank FIRST ORDER BY row_1 DESC) period,
 46          MIN(t2.time)KEEP(dense_rank FIRST ORDER BY row_1 DESC) TIME,
 47          t2.datetime
 48     FROM t2
 49    WHERE t2.area = 0
 50      AND t2.row_2 = 0
 51    GROUP BY t2.junc,
 52             t2.area,
 53             t2.datetime
 54   UNION
 55   SELECT t2.junc,
 56          t2.area,
 57          MIN(t2.period)KEEP(dense_rank FIRST ORDER BY row_2 DESC) period,
 58          MIN(t2.time)KEEP(dense_rank FIRST ORDER BY row_2 DESC) TIME,
 59          t2.datetime
 60     FROM t2
 61    WHERE t2.area = 0
 62      AND t2.m_row_1 = 0
 63    GROUP BY t2.junc,
 64             t2.area,
 65             t2.datetime
 66  ;      JUNC       AREA     PERIOD       TIME DATETIME
---------- ---------- ---------- ---------- -------------------
         1          0          3        750 2011/07/17 15:36:00
         2          0          3       1430 2011/07/17 15:36:00
         3          0          2        360 2011/07/17 15:36:00
         4          0          5        360 2011/07/17 15:36:00
         5          0          4       1200 2011/07/17 15:36:00
         6          0          3        360 2011/07/17 15:36:00
         7          0          3        360 2011/07/17 15:36:00
         8          0          4        360 2011/07/17 15:36:00
         9          0          3        750 2011/07/17 15:36:00
        10          0          2        750 2011/07/17 15:36:00已选择10行。

Answer 3

SQL> WITH t AS (
  2  SELECT 0 area,'1' junc,1170 TIME,'3' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
  3  SELECT 0 area,'1' junc,740  TIME,'3' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
  4  SELECT 0 area,'1' junc,750  TIME,'3' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
  5  SELECT 0 area,'1' junc,1350 TIME,'3' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
  6  SELECT 0 area,'1' junc,1440 TIME,'3' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
  7  SELECT 0 area,'2' junc,1430 TIME,'3' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
  8  SELECT 0 area,'3' junc,1440 TIME,'2' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
  9  SELECT 0 area,'3' junc,360  TIME,'2' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
 10  SELECT 0 area,'3' junc,1320 TIME,'2' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
 11  SELECT 0 area,'4' junc,1440 TIME,'5' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
 12  SELECT 0 area,'4' junc,360  TIME,'5' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
 13  SELECT 0 area,'4' junc,1320 TIME,'5' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
 14  SELECT 0 area,'5' junc,1440 TIME,'4' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL UNION ALL
 15  SELECT 0 area,'5' junc,1200 TIME,'4' period,TO_DATE('2011/7/17 15:36:00','yyyy/mm/dd hh24:mi:ss') datetime FROM DUAL
 16  )
 17  SELECT m.*
 18    FROM (SELECT t.*,
 19                 RANK() OVER(PARTITION BY t.junc ORDER BY CASE WHEN (trunc(sysdate) + t.time / 60 / 24) <= SYSDATE THEN 0 ELSE t.TIME END) rn
 20            FROM t ) m
 21   WHERE m.rn = 1
 22   ORDER BY m.datetime
 23  ;      AREA JUNC       TIME PERIOD DATETIME            RN
---------- ---- ---------- ------ ----------- ----------
         0 1           740 3      2011/07/17           1
         0 1           750 3      2011/07/17           1
         0 5          1200 4      2011/07/17           1
         0 3           360 2      2011/07/17           1
         0 4           360 5      2011/07/17           1
         0 2          1430 3      2011/07/17           16 rows selected

Answer 4

to：BenChiM888
这么复杂啊，既然没看明白

Answer 5

--看不懂就写个简单的，按照你的逻辑来
[TEST@orcl] SQL>with juncperiod as(
  2  select 0 area, 1  junc, 1170 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  3  select 0 area, 1  junc, 750  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  4  select 0 area, 1  junc, 1350 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  5  select 0 area, 1  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  6  select 0 area, 2  junc, 1430 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  7  select 0 area, 3  junc, 1440 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  8  select 0 area, 3  junc, 360  time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
  9  select 0 area, 3  junc, 1320 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 10  select 0 area, 4  junc, 1440 time,5 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 11  select 0 area, 4  junc, 360  time,5 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 12  select 0 area, 4  junc, 1320 time,5 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 13  select 0 area, 5  junc, 1440 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 14  select 0 area, 5  junc, 1200 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 15  select 0 area, 6  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 16  select 0 area, 6  junc, 1320 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 17  select 0 area, 6  junc, 360  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 18  select 0 area, 7  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 19  select 0 area, 7  junc, 360  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 20  select 0 area, 7  junc, 1320 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 21  select 0 area, 8  junc, 1440 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 22  select 0 area, 8  junc, 1320 time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 23  select 0 area, 8  junc, 360  time,4 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 24  select 0 area, 9  junc, 1440 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 25  select 0 area, 9  junc, 750  time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 26  select 0 area, 9  junc, 1170 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 27  select 0 area, 9  junc, 1350 time,3 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 28  select 0 area, 10 junc, 1440 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 29  select 0 area, 10 junc, 750  time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 30  select 0 area, 10 junc, 1170 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual union all
 31  select 0 area, 10 junc, 1350 time,2 period, to_date('2011/7/17 15:36','yyyy/mm/dd hh24:mi:ss')datetime from dual
 32  )SELECT m.*
 33       FROM (SELECT t.*,
 34                    first_value(t.time) OVER(PARTITION BY t.junc ORDER BY CASE WHEN (trunc(sysdate) + t.time / 60 / 24) <= SYSDATE THEN 9
999+t.TIME ELSE t.TIME END DESC) first_value
 35               FROM juncperiod t ) m
 36      WHERE m.first_value = TIME
 37      ORDER BY m.junc
 38     ;      AREA       JUNC       TIME     PERIOD DATETIME            FIRST_VALUE
---------- ---------- ---------- ---------- ------------------- -----------
         0          1        750          3 2011/07/17 15:36:00         750
         0          2       1430          3 2011/07/17 15:36:00        1430
         0          3        360          2 2011/07/17 15:36:00         360
         0          4        360          5 2011/07/17 15:36:00         360
         0          5       1440          4 2011/07/17 15:36:00        1440
         0          6        360          3 2011/07/17 15:36:00         360
         0          7        360          3 2011/07/17 15:36:00         360
         0          8        360          4 2011/07/17 15:36:00         360
         0          9        750          3 2011/07/17 15:36:00         750
         0         10        750          2 2011/07/17 15:36:00         750已选择10行。

Answer 6

如果有下面这样的数据，你的想法就有问题了，楼主想要第二条看他自己写的那个SQL的desc
area junc time period datetime
0 1 740 3 2011/7/17 15:36
0 1 750 3 2011/7/17 15:36

Answer 7

junc中的序号不连续，不是我想要的结果。 还是要谢谢你

Answer 8

2
3
2
3
3
3
2
4
2
4
750
360
1430
1200
你要找的数据是不是以上序列,数值为以上数值的记录?
如果是的话,根据你的语句.(select trunc(sysdate) + t.time / 60 / 24 from dual) <= sysdate
是要找出T.time能被1440整除后大于0的记录.这与结果是相反的.要得到以上值用以下语句.
select area,junc,time,period,datetime from juncperiod where area=0 and (time=360 or 
time=750 or time=1430 or 1200);

Answer 9

select trunc(sysdate) + t.time / 60 / 24 from dual) <= sysdate
这个是把junc中的整型值换算成系统时间然后跟当前时间进行比较