（１００分）求助Oracle查询，在线等　急

有一个数据表数据如下：
id Sub_id Prgm_id Start_date 　　　　 End_date
1 Sub153 1 2007-8-20 00:20:00 2007-8-20 01:20:00
2 Sub253 24 2007-8-6 9:01:00 2007-8-6 03:20:00
3 Sub435 2 2007-8-20 02:20:00 2007-8-20 06:20:00
4 Sub232 15 2007-11-20 08:20:00 2007-11-20 10:58:00
5 Sub243 1 2007-11-20 12:20:00 2007-11-20 14:00:32
6 Sub253 24 2007-11-23 15:01:00 2007-11-23 17:20:00
7 Sub243 13 2007-12-23 23:20:00 2007-12-24 02:09:32
. . . . .
. . . . .现在要按２４小时进行分组查询，得出每个时段的在线人数。如上面的数据来说，应该得出下面结果：
hour num 说明NUM计算值
00 1 ID 为7
01 3 ID 为1,2,7
02 2 ID 为2,7
03 2 ID 为2,3
04 1 ID 为3
05 1 ID 为3
06 1 ID 为3
07 0
08 0
09 1 ID 为4
10 1 ID 为4
11 0
12 0
13 1 ID 为5
14 1 ID 为5
15 0
16 1 ID 为6
17 1 ID 为6
18 0
19 0
20 0
21 0
22 0
23 0
说明：
 1.如果是算０点时间的，像第一行记录    id Sub_id Prgm_id Start_date End_date
    1 Sub153 1 2007-8-20 00:20:00 2007-8-20 01:20:00   因为它的时间段是在00:20－01:20分，而0点整不在那个时间段中，所以这一行记录不能数。 2.再拿第七条记录来说：
    
    id Sub_id Prgm_id Start_date End_date
    7 Sub243 13 2007-12-23 23:20:00 2007-12-24 02:09:32
   它的时间段是23:20－02:09，,所以00、01、02都在这段时间中。现在问题来了，请问这一条SQL语句怎么写，求助各位大侠，请大侠们拔刀相助。
如果不方便写SQL语句，也可以变动数据表

解决方案 »

  1.   

    刚才发贴时表格有一点乱再发一下表格数据
    id Sub_id  Prgm_id  　　 Start_date              End_date
    1 Sub153   1       2007-8-20 00:20:00      2007-8-20 01:20:00
    2 Sub253   24       2007-8-6 9:01:00       2007-8-6 03:20:00
    3 Sub435   2       2007-8-20 02:20:00      2007-8-20 06:20:00
    4 Sub232   15       2007-11-20 08:20:00     2007-11-20 10:58:00
    5 Sub243   1       2007-11-20 12:20:00     2007-11-20 14:00:32
    6 Sub253   24       2007-11-23 15:01:00     2007-8-6 17:20:00
    7 Sub243   13       2007-12-23 23:20:00     2007-11-24 02:09:32
    . . . . .
    . . . . .现在要按２４小时进行分组查询，得出每个时段的在线人数。如上面的数据来说，应该得出下面结果： hour        num        说明NUM计算值
    00          1          ID 为7
    01          3          ID 为1,2,7
    02          2          ID 为2,7
    03          2          ID 为2,3
    04          1          ID 为3
    05          1          ID 为3
    06          1          ID 为3
    07          0          
    08          0          
    09          1          ID 为4
    10          1          ID 为4
    11          0          
    12          0          
    13          1          ID 为5
    14          1          ID 为5
    15          0          
    16          1          ID 为6
    17          1          ID 为6
    18          0          
    19          0          
    20          0          
    21          0          
    22          0          
    23          0          说明： 
      1.如果是算０点时间的，像第一行记录         id Sub_id Prgm_id Start_date End_date 
            1 Sub153 1 2007-8-20   00:20:00 2007-8-20   01:20:00       因为它的时间段是在00:20－01:20分，而0点整不在那个时间段中，所以这一行记录不能数。   2.再拿第七条记录来说： 
            
            id Sub_id Prgm_id Start_date End_date 
            7 Sub243 13 2007-12-23   23:20:00 2007-12-24   02:09:32 
          它的时间段是23:20－02:09，,所以00、01、02都在这段时间中。 现在问题来了，请问这一条SQL语句怎么写，求助各位大侠，请大侠们拔刀相助。 
    如果不方便写SQL语句，也可以变动数据表 
      

  2.   

    只会横过来写select 
    sum(case when to_char(Start_date,'hh24:mi:ss')<='00:00:00' and to_char(End_date,'hh24:mi:ss')<='00:00:00' then 1 else 0 end) as 00,
    sum(case when to_char(Start_date,'hh24:mi:ss')<='01:00:00' and to_char(End_date,'hh24:mi:ss')<='01:00:00' then 1 else 0 end) as 01,
    ...
    from table
    仅恭参考
      

  3.   

    谢谢“HelloWorld_001”的回复，横过来的确是可以，但是SQL语句这样写出来将会有一百多行，不知其它人是否还有好的方法。
      

  4.   

    --这个是sql server中的写法,你参考一下.
    比如有一个表timeTable,有字段id,score,inputDate.
    数据如下
      id       score    inputDate
      1        2         '2007-4-5 3:33:33'
      2        1         '2007-4-5 4:33:33'
      3        4         '2007-3-5 3:33:33'
      4        2         '2007-4-2 2:33:33'
    我要按时间分组统计score，结果如下
    0:00-1:00   0
    1:00-2:00   0
    2:00-3:00   2
    3:00-4:00   6
    4:00-5:00   1
    5:00-6:00   0
    ....
    23:00-24:00 0declare @a table(id int, score int, inputDate smalldatetime)
    insert @a select 1, 2, '2007-4-5 3:33:33'
    union all select 2, 1, '2007-4-5 4:33:33'
    union all select 3, 4, '2007-3-5 3:33:33'
    union all select 4, 2, '2007-4-2 2:33:33'select right(100+a,2)+':00-'+right(100+b,2)+':00',
    sum(case when datepart(hour,inputdate) >=a and datepart(hour,inputdate)<b then score else 0 end) 
    from @a a ,
    (select 0 a,1 b 
    union all select  1,2
    union all select  2,3
    union all select  3,4
    union all select  4,5
    union all select  5,6
    union all select  6,7
    union all select  7,8
    union all select  8,9
    union all select  9,10
    union all select  10,11
    union all select  11,12
    union all select  12,13
    union all select  13,14
    union all select  14,15
    union all select  15,16
    union all select  16,17
    union all select  17,18
    union all select  18,19
    union all select  19,20
    union all select  20,21
    union all select  21,22
    union all select  22,23
    union all select  23,24
    )aa
    group by right(100+a,2)+':00-'+right(100+b,2)+':00'--------------- ----------- 
    00:00-01:00     0
    01:00-02:00     0
    02:00-03:00     2
    03:00-04:00     6
    04:00-05:00     1
    05:00-06:00     0
    06:00-07:00     0
    07:00-08:00     0
    08:00-09:00     0
    09:00-10:00     0
    10:00-11:00     0
    11:00-12:00     0
    12:00-13:00     0
    13:00-14:00     0
    14:00-15:00     0
    15:00-16:00     0
    16:00-17:00     0
    17:00-18:00     0
    18:00-19:00     0
    19:00-20:00     0
    20:00-21:00     0
    21:00-22:00     0
    22:00-23:00     0
    23:00-24:00     0（所影响的行数为 24 行）
      

  5.   

    哎~~ 写了我不少的时间 ..XD,你的数据好像不是很正确,开始时间和结束时间不合逻辑,我相应的改了一下,你试试看,我测试的SQL是正确的 ...
    SQL> select * from datetest;       SID SUB_ID        PRGM_ID START_DATE  END_DATE
    ---------- ---------- ---------- ----------- -----------
             1 Sub153              1 2007-8-20 0 2007-8-20 1
             2 Sub253             24 2007-8-6 9: 2007-8-6 13
             3 Sub435              2 2007-8-20 2 2007-8-20 6
             4 Sub232             15 2007-11-20  2007-11-20
             5 Sub243              1 2007-11-20  2007-11-20
             6 Sub253             24 2007-11-23  2007-11-23
             7 Sub243             13 2007-12-23  2007-11-247 rows selectedSQL> 
    SQL> select yy.rn_1,
      2         nvl(zz.mycounts,0) as num,
      3         decode(zz.ids,null,null,'ID 为'||zz.ids) as "说明NUM计算值"
      4    from (
      5          select distinct
      6                 mm.myhours,
      7                 mm.mycounts,
      8                 first_value(mm.path) over(partition by mm.myhours order by mm.lev desc) as ids
      9            from (
     10                  select rr.myhours,
     11                         rr.mycounts,
     12                         ltrim(sys_connect_by_path(rr.sid,','),',') as path,
     13                         level lev
     14                    from (
     15                          select uu.*,
     16                                 uu.myhours||(row_number() over(partition by uu.myhours order by uu.myhours)-1) as front_hours,
     17                                 uu.myhours||row_number() over(partition by uu.myhours order by uu.myhours) as behind_hours,
     18                                 count(*) over(partition by uu.myhours order by uu.myhours) as mycounts
     19                            from (
     20                                  select lpad(decode(sign(to_char(dt.Start_date,'HH24') + tt.rn-24),
     21                                                -1,to_char(dt.Start_date,'HH24') + tt.rn,
     22                                                to_char(dt.Start_date,'HH24') + tt.rn-24),2,'0') as myhours,
     23                                         dt.sid
     24                                    from (
     25                                          select dd.*,
     26                                                 decode(sign(to_char(end_date,'hh24')-to_char(Start_date,'hh24')),
     27                                                        -1,to_char(end_date,'hh24')-to_char(Start_date,'hh24')+24,
     28                                                        to_char(end_date,'hh24')-to_char(Start_date,'hh24')) as diffhours
     29                                            from datetest dd
     30                                         )dt,
     31                                         (
     32                                          select rownum rn
     33                                            from all_objects
     34                                           where rownum <= 24
     35                                         )tt
     36                                   where tt.rn <= dt.diffhours
     37                                  )uu
     38                          )rr
     39                   connect by prior rr.front_hours = rr.behind_hours
     40                  )mm
     41          )zz,
     42          (
     43            select lpad(rownum-1,2,'0') rn_1
     44              from all_objects
     45             where rownum <= 24
     46          )yy
     47   where yy.rn_1 = zz.myhours(+)
     48   order by 1;RN_1        NUM 说明NUM计算值
    ---- ---------- --------------------------------------------------------------------------------
    00            1 ID 为7
    01            2 ID 为1,7
    02            1 ID 为7
    03            1 ID 为3
    04            1 ID 为3
    05            1 ID 为3
    06            1 ID 为3
    07            0 
    08            0 
    09            1 ID 为4
    10            2 ID 为4,2
    11            1 ID 为2
    12            1 ID 为2
    13            2 ID 为5,2
    14            1 ID 为5
    15            0 
    16            1 ID 为6
    17            1 ID 为6
    18            0 
    19            0 RN_1        NUM 说明NUM计算值
    ---- ---------- --------------------------------------------------------------------------------
    20            0 
    21            0 
    22            0 
    23            0 24 rows selected
      

  6.   

      谢谢各位的回复，因为双休，所以没有过来“颁奖”，请大侠们见谅。现在补上了。
      
      最后还要特意谢谢“枫の叶”，一看就知道花了不少时间。其实你误会了我的意思了，算出结果的“说明NUM计算值”这一列是不要显示出来的，我本意只是想说明那个NUM列值由来的。不过还是要佩服，这么复杂的SQL都能写出来，的确是牛人，呵呵！再次谢过了。