按每3个小时的统计

[lxq19851204]

create table D1 (`TransDate` datetime default NULL);
insert into D1 value ('2013-12-01 10:12:20'),('2013-12-01 11:01:02'),('2013-12-01 13:02:05')
,('2013-12-01 13:08:05'),('2013-12-01 14:02:05'),('2013-12-01 15:02:05'),('2013-12-01 16:02:05'
);


mysql> select * from d1;
+---------------------+
| TransDate           |C
+---------------------+
| 2013-12-01 10:12:20 |10:00:00-12:00:00
| 2013-12-01 11:01:02 |10:00:00-12:00:00
| 2013-12-01 13:02:05 |13:00:00-15:00:00
| 2013-12-01 13:08:05 |13:00:00-15:00:00
| 2013-12-01 14:02:05 |13:00:00-15:00:00
| 2013-12-01 15:02:05 |15:00:00-17:00:00
| 2013-12-01 16:02:05 |15:00:00-17:00:00
+---------------------+

[WWWWA]

SELECT *, IF(HOUR(`TransDate`) BETWEEN 10 AND 12,'10:00:00-12:00:00', IF(HOUR(`TransDate`) BETWEEN 13 AND 14,'13:00:00-15:00:00', IF(HOUR(`TransDate`) BETWEEN 15 AND 17,'15:00:00-17:00:00', ''))) AS C

[lxq19851204]

我是要按3个小时来统计 C是根据Transdate在哪个时间段，自动划分区间 2013-12-01 10:12:20 |10:00:00-12:00:00 | 2013-12-01 11:01:02 |10:00:00-12:00:00 | 2013-12-01 13:02:05 |13:00:00-15:00:00 | 2013-12-01 13:08:05 |13:00:00-15:00:00 | 2013-12-01 14:02:05 |13:00:00-15:00:00 | 2013-12-01 15:02:05 |15:00:00-17:00:00 | 2013-12-01 16:02:05 |15:00:00-17:00:00

[rucypli]

没看明白这是要干啥

[lxq19851204]

按每3个小时统计总数.开始时间不确定.

[lxq19851204]

mysql> SELECT a.transdate,CONCAT(
    -> date_format(date_sub(min(C.TRANSDATE),interval -
    -> if(floor((hour(a.transdate)-hour(b.transdate))/3)>=0,
    -> (floor((hour(a.transdate)-hour(b.transdate))/3))*3 ,(floor((hour(a.transd
ate)-hour(b.transdate))/3)+8)*3) hour),'%H:00:00')
    -> ,'-',
    -> date_format(date_sub(min(C.TRANSDATE),interval -
    -> if(floor((hour(a.transdate)-hour(b.transdate))/3)>=0,
    -> (floor((hour(a.transdate)-hour(b.transdate))/3)+1)*3 ,(floor((hour(a.tran
sdate)-hour(b.transdate))/3)+9)*3) hour),'%H:00:00')
    -> ) AS C
    -> FROM d1 a,d1 b,d1 C
    -> where a.transdate>=b.transdate
    -> group by a.transdate;
+---------------------+-------------------+
| transdate           | C                 |
+---------------------+-------------------+
| 2013-12-01 10:12:20 | 10:00:00-13:00:00 |
| 2013-12-01 11:01:02 | 10:00:00-13:00:00 |
| 2013-12-01 13:02:05 | 13:00:00-16:00:00 |
| 2013-12-01 13:08:05 | 13:00:00-16:00:00 |
| 2013-12-01 14:02:05 | 13:00:00-16:00:00 |
| 2013-12-01 15:02:05 | 13:00:00-16:00:00 |
| 2013-12-01 16:02:05 | 16:00:00-19:00:00 |
| 2013-12-01 19:00:02 | 19:00:00-22:00:00 |
| 2013-12-01 23:00:02 | 22:00:00-01:00:00 |
| 2013-12-02 05:00:02 | 04:00:00-07:00:00 |
+---------------------+-------------------+
10 rows in set (0.00 sec)

mysql>

这样感觉速度很慢

[yumenfeiyu945]

这是要干嘛，算出时间在哪个区间？ select transdate,concat(floor((hour(transdate)-1)/3)*3+1,':00:00-',floor(((hour(transdate)-1)/3)*3+4)%24,':00:00') from d1