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

解决方案 »

  1.   

    我是要按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
      

  2.   


    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>这样感觉速度很慢
      

  3.   

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