现有表如下:
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
解决方案 »
- 连接密码为空的Mysql数据库,提示错误怎么回事?
- Mysql 的rank 函数实现的问题
- 有在Windows平台下用SQL Relay的大牛没?
- 如何修改已发布产品的数据库?
- postgresql 数据库能不能实现类似于select top 10这样的功能,我试不出来
- 求一条语句实现的问题
- 新建表时能不能这样指定字段的缺省值
- PHP连接MySql时的错误,在线等
- MYSQL 某列有大量空值是不是就
- mysql安装需要提供oracle凭证
- 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点又恰巧没有记录,以此类推,我需要获取该时间段上面最近的一次存在的记录