SQL 按時間段來查詢

Question

如表1:
2007-6-27 10:00           20
2007-6-27 11:00           60
2007-6-27 11:25           15
2007-6-27 12:05           100
2007-6-27 12:59           10
2007-6-27 13:00           150出來的效果是要2007-6-27 10:00-10:59     20
2007-6-27 11:00-11:59     75
2007-6-27 12:00-12:59     110
2007-6-27 13:00-13:59     150
我想要這樣的效果....怎樣做呢???謝謝

Answer 1

在oracle中可以这样实现create table t(cdate date,cnum int);
/
insert into t
select to_date('2007-6-27 10:00','yyyy-mm-dd HH24:MI'),20 from dual union all
select to_date('2007-6-27 11:00','yyyy-mm-dd HH24:MI'),60 from dual union all
select to_date('2007-6-27 11:25','yyyy-mm-dd HH24:MI'),15 from dual union all
select to_date('2007-6-27 12:05','yyyy-mm-dd HH24:MI'),100 from dual union all
select to_date('2007-6-27 12:59','yyyy-mm-dd HH24:MI'),10 from dual union all
select to_date('2007-6-27 13:00','yyyy-mm-dd HH24:MI'),150 from dual;
/
select to_char( cdate,'yyyy-mm-dd' )||' '||to_char( cdate,'HH24' )||':00-'||to_char( cdate,'HH24' )||':59', sum(cnum)  from t group by to_char( cdate,'yyyy-mm-dd' ),to_char( cdate,'HH24' )
--执行结果
2007-06-27 10:00-10:59 20
2007-06-27 11:00-11:59 75
2007-06-27 12:00-12:59 110
2007-06-27 13:00-13:59 150

Answer 2

你看看这个语句在sqlserver中是否可以执行，我这里没有sqlserverselect convert(varchar(10),cdate,120)+' '+datename(hour,cdate)+':00-'+datename(hour,cdate)+':59', sum(cnum)  from t group by convert(varchar(10),cdate,120),datename(hour,cdate);

Answer 3

那如果我想要下面的結果呢?
2007-06-27 00:00-00:59 0
2007-06-27 01:00-01:59 0
2007-06-27 02:00-02:59 0
2007-06-27 03:00-03:59 0
......
2007-06-27 10:00-10:59 20
2007-06-27 11:00-11:59 75
2007-06-27 12:00-12:59 110
2007-06-27 13:00-13:59 150
......
2007-06-27 22:00-22:59 0
2007-06-27 23:00-23:59 0就是每天24個小時都做一個report
如果那個小時沒有產生記錄就為0

Answer 4

--执行查询
select to_char( cdate,'yyyy-mm-dd' )||' '||to_char( cdate,'HH24' )||':00-'||to_char( cdate,'HH24' )||':59', 
sum(cnum) from
(select cdate,cnum from t
union all
select to_date('2007-06-27'||' 00','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 01','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 02','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 03','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 04','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 05','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 06','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 07','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 08','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 09','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 10','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 11','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 12','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 13','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 14','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 15','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 16','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 17','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 18','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 19','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 20','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 21','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 22','yyyy-mm-dd HH24') cdate,0 cnum from dual union all
select to_date('2007-06-27'||' 23','yyyy-mm-dd HH24') cdate,0 cnum from dual
)t
group by to_char( t.cdate,'yyyy-mm-dd' ),to_char( t.cdate,'HH24' )
--查询结果
1 2007-06-27 00:00-00:59 0
2 2007-06-27 01:00-01:59 0
3 2007-06-27 02:00-02:59 0
4 2007-06-27 03:00-03:59 0
5 2007-06-27 04:00-04:59 0
6 2007-06-27 05:00-05:59 0
7 2007-06-27 06:00-06:59 0
8 2007-06-27 07:00-07:59 0
9 2007-06-27 08:00-08:59 0
10 2007-06-27 09:00-09:59 0
11 2007-06-27 10:00-10:59 20
12 2007-06-27 11:00-11:59 75
13 2007-06-27 12:00-12:59 110
14 2007-06-27 13:00-13:59 150
15 2007-06-27 14:00-14:59 0
16 2007-06-27 15:00-15:59 0
17 2007-06-27 16:00-16:59 0
18 2007-06-27 17:00-17:59 0
19 2007-06-27 18:00-18:59 0
20 2007-06-27 19:00-19:59 0
21 2007-06-27 20:00-20:59 0
22 2007-06-27 21:00-21:59 0
23 2007-06-27 22:00-22:59 0
24 2007-06-27 23:00-23:59 0