表名为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的行。
不知列位能否指点一二...
在线等
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的行。
不知列位能否指点一二...
在线等
如:
当前时间为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值转换后的时间时,列出结果;如果没有比当前时间小的,则列出大于当前时间的结果。大于当前时间的结果可能有多个,列出大于并最靠近当前时间的那一条记录。
[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行。
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
这么复杂啊,既然没看明白
[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行。
如果有下面这样的数据,你的想法就有问题了,楼主想要第二条看他自己写的那个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
junc中的序号不连续,不是我想要的结果。 还是要谢谢你
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);
select trunc(sysdate) + t.time / 60 / 24 from dual) <= sysdate
这个是把junc中的整型值换算成系统时间然后跟当前时间进行比较