现有表如下:
id    WATCH_TIME        ONLINE_COUNT
1    2012-3-7 16:40:42    1
2    2012-3-7 17:48:55    2
3    2012-3-7 18:48:07    1编写如下sql代码:
SELECT (case when SUBSTR(WATCH_TIME,12,8)>='16:00:00' and SUBSTR(WATCH_TIME,12,8)<='16:59:59'  then ONLINE_COUNT else 0 end) as h16,
(case when SUBSTR(WATCH_TIME,12,8)>='17:00:00' and SUBSTR(WATCH_TIME,12,8)<='17:59:59'  then ONLINE_COUNT else 0 end)as h17,
(case when SUBSTR(WATCH_TIME,12,8)>='18:00:00' and SUBSTR(WATCH_TIME,12,8)<='18:59:59'  then ONLINE_COUNT else 0 end)as h18,
(case when SUBSTR(WATCH_TIME,12,8)>='19:00:00' and SUBSTR(WATCH_TIME,12,8)<='19:59:59'  then ONLINE_COUNT else 0 end)as h19
FROM `t_log_online_count` where date(WATCH_TIME)=date('2012-03-07');获取结果如下:
h16   h17   h18   h19
1      0    0     0
0      2    0     0
0      0    1     0期望获取的结果如下:
h16   h17   h18   h19
1      2    1      0问题1:怎么将查询所在时间段的在线人数只在一行显示?
问题2:如果在某个时间段(如19点0分0秒到19点59分59秒)没有该记录,怎么设置为上一次最近的在线人数,即后面的'h19'字段的值应该为1

解决方案 »

  1.   

    1
    select max(h16),max(17),max(18),max(19)from
    (SELECT (case when SUBSTR(WATCH_TIME,12,8)>='16:00:00' and SUBSTR(WATCH_TIME,12,8)<='16:59:59' then ONLINE_COUNT else 0 end) as h16,
    (case when SUBSTR(WATCH_TIME,12,8)>='17:00:00' and SUBSTR(WATCH_TIME,12,8)<='17:59:59' then ONLINE_COUNT else 0 end)as h17,
    (case when SUBSTR(WATCH_TIME,12,8)>='18:00:00' and SUBSTR(WATCH_TIME,12,8)<='18:59:59' then ONLINE_COUNT else 0 end)as h18,
    (case when SUBSTR(WATCH_TIME,12,8)>='19:00:00' and SUBSTR(WATCH_TIME,12,8)<='19:59:59' then ONLINE_COUNT else 0 end)as h19
    FROM `t_log_online_count` where date(WATCH_TIME)=date('2012-03-07')) a2
    SELECT (case when SUBSTR(WATCH_TIME,12,8)>='16:00:00' and SUBSTR(WATCH_TIME,12,8)<='16:59:59' then ONLINE_COUNT else 0 end) as h16,
    (case when SUBSTR(WATCH_TIME,12,8)>='17:00:00' and SUBSTR(WATCH_TIME,12,8)<='17:59:59' then ONLINE_COUNT else 0 end)as h17,
    (case when SUBSTR(WATCH_TIME,12,8)>='18:00:00' and SUBSTR(WATCH_TIME,12,8)<='18:59:59' then ONLINE_COUNT else 0 end)as h18,
    if((select count(*) from `t_log_online_count` where SUBSTR(WATCH_TIME,12,8)>='19:00:00' and SUBSTR(WATCH_TIME,12,8)<='19:59:59')=0,h18,
    (case when SUBSTR(WATCH_TIME,12,8)>='19:00:00' and SUBSTR(WATCH_TIME,12,8)<='19:59:59' then ONLINE_COUNT else 0 end))as h19
    FROM `t_log_online_count` where date(WATCH_TIME)=date('2012-03-07')
      

  2.   

    首先谢谢你热心的回答!
    问题1已经OK!
    问题2没有测试成功,可能你的if条件判断写的不是很完整。另外,在这个表中我只列出了4个时间段的数据,实际应用中可能需要更多,那么你的if条件判断怎么能够更通用呢?还有,假如在19点这个时间段没有记录,而18点又恰巧没有记录,以此类推,我需要获取该时间段上面最近的一次存在的记录