select ID,TESTTIME from st_SpeedTestLog; ID TESTTIME
---------- -------------------
7 2011-11-21 11:57:04
8 2011-11-22 11:57:31
9 2011-11-23 12:03:40
11 2011-11-23 12:06:01
13 2011-11-23 12:16:00
14 2011-11-25 12:18:38
1 2011-11-26 11:40:16
2 2011-11-26 11:47:53
3 2011-11-26 11:48:38
23 2011-11-27 14:40:19
24 2011-12-03 15:08:03
25 2011-12-03 15:08:51
4 2011-12-05 11:51:16
5 2011-12-05 11:53:40
6 2011-12-05 11:54:09
10 2011-12-07 12:04:23
12 2011-12-07 12:08:41
22 2011-12-30 14:05:57
15 2011-12-30 12:25:40
16 2011-12-30 12:26:05
17 2011-12-30 13:30:44
18 2011-12-30 13:40:17
19 2011-12-30 13:43:14
20 2011-12-30 13:44:10
21 2011-12-31 14:04:18
请问各位大师 我如何取2011-11-23 12:00:00到2011-12-30 14:00:00之间,按周进行分组统计个数
展示如下:
week count
2011-11-23 12:03:40 - 2011-11-27 23:59:59 7
2011-11-27 00:00:00 - 2011-12-04 23:59:59 3
2011-12-04 00:00:00 - 2011-12-10 23:59:59 0求sql语句
---------- -------------------
7 2011-11-21 11:57:04
8 2011-11-22 11:57:31
9 2011-11-23 12:03:40
11 2011-11-23 12:06:01
13 2011-11-23 12:16:00
14 2011-11-25 12:18:38
1 2011-11-26 11:40:16
2 2011-11-26 11:47:53
3 2011-11-26 11:48:38
23 2011-11-27 14:40:19
24 2011-12-03 15:08:03
25 2011-12-03 15:08:51
4 2011-12-05 11:51:16
5 2011-12-05 11:53:40
6 2011-12-05 11:54:09
10 2011-12-07 12:04:23
12 2011-12-07 12:08:41
22 2011-12-30 14:05:57
15 2011-12-30 12:25:40
16 2011-12-30 12:26:05
17 2011-12-30 13:30:44
18 2011-12-30 13:40:17
19 2011-12-30 13:43:14
20 2011-12-30 13:44:10
21 2011-12-31 14:04:18
请问各位大师 我如何取2011-11-23 12:00:00到2011-12-30 14:00:00之间,按周进行分组统计个数
展示如下:
week count
2011-11-23 12:03:40 - 2011-11-27 23:59:59 7
2011-11-27 00:00:00 - 2011-12-04 23:59:59 3
2011-12-04 00:00:00 - 2011-12-10 23:59:59 0求sql语句
解决方案 »
- Oracle用户、模式以及模式对象的关系
- mount.ocfs2: Device name specified was not found while opening device /dev/sdb2
- 用存储过程动态创建触发器来记录表数据的更新,巨头疼的问题,高分求解!!!
- 两台机器上装有不同ORACLE版本,相互间无法连接
- 如何对数据库压力测试?
- 如何替换字段里的时间?如把03/03/2003换成01/02/2003?
- 关于日期型和字符型数据在查询中速度的对比,请专家说说
- 急求一SQL,在线等待,各位大侠请帮帮忙啊!!
- PHP和oracle数据库连接后,用户名和密码无效是咋回事?能正常登录sql\PLUS呀
- 重建索引经典问题:马上结贴送分,都可以看过来哦!
- oracle 删除数据闪回问题
- oracle11g监听问题
with st_SpeedTestLog as
(
select 1 id,to_date('2011-11-21 11:57:04','yyyy-mm-dd hh24:mi:ss') TESTTIME from dual
union all
select 2 id,to_date('2011-11-22 11:57:31','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 3 id,to_date('2011-11-27 14:40:19','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 4 id,to_date('2011-12-03 15:08:03','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 5 id,to_date('2011-12-03 15:08:03','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 6 id,to_date('2011-12-05 11:53:40','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 7 id,to_date('2011-12-07 12:04:23','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 7 id,to_date('2011-12-07 12:08:41','yyyy-mm-dd hh24:mi:ss') from dual
)select to_char(b_date,'yyyy-mm-dd')||' — '||to_char(e_date-1,'yyyy-mm-dd') t_date,count(1)
from
(
select n_time+(level-1)*7 b_date,
n_time+level*7 e_date
from (
select to_date(to_char(min(TESTTIME),'yyyy-mm-dd'),'yyyy-mm-dd') n_time,
to_date(to_char(max(TESTTIME),'yyyy-mm-dd'),'yyyy-mm-dd') m_time
from st_SpeedTestLog
)
connect by level < (m_time-n_time)/7+1
) t1 left join st_SpeedTestLog t2 on t2.TESTTIME between t1.b_date and t1.e_date
group by to_char(b_date,'yyyy-mm-dd')||' — '||to_char(e_date-1,'yyyy-mm-dd')
order by to_char(b_date,'yyyy-mm-dd')||' — '||to_char(e_date-1,'yyyy-mm-dd')
t_date t_num
----------------------------------------------
1 2011-11-21 — 2011-11-27 3
2 2011-11-28 — 2011-12-04 2
3 2011-12-05 — 2011-12-11 3