现有表如下:
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
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
解决方案 »
- jsp连接mysql 数据库,老是异常,查看端口如下
- 有没有可以查看某个数据库所有表的查询次数
- MYSQL4.1数据库里的数据表内容出现乱码,默认是latin1,想要utf8编码(1064 错误)在线等!
- 关于建立网站数据库
- 为什么我插入到MYSQL中的文字会变成问号啊?怎么解决啊??
- MySQL创始人发邮件寻求中国帮助!
- 请朋友们给我解释一下 呵呵
- 谁能帮我看看下面这条SQL语句为什么不能执行!
- 如何删除'含有空格的表名'?
- [求助]mysql 不同库相同表主从复制
- Can't connect to MySQL server on '183.37.81.152' (10060)
- 小白求帮忙,想做一个东西但是不知道应该学习什么?
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')
问题1已经OK!
问题2没有测试成功,可能你的if条件判断写的不是很完整。另外,在这个表中我只列出了4个时间段的数据,实际应用中可能需要更多,那么你的if条件判断怎么能够更通用呢?还有,假如在19点这个时间段没有记录,而18点又恰巧没有记录,以此类推,我需要获取该时间段上面最近的一次存在的记录