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
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
解决方案 »
- MYSQL中VARCHAR字段排序的问题
- Mysql 更改了root的权限 无法登陆.100分
- 用存储过程实现批量更新是否有必要
- mysql 能否在int(10)类型中取得前八位建立索引?
- Debian安装Mysql5.1,求救~!:-(99999999
- 请问这个错误是什么意思?谢谢。mysql
- 怎么设置MYSQL的密码?
- tomcat+mysql+jdbc为什么会出现这个问题?
- mysql 5.6.22 centos 7下安装完成后 默认密码是什么 在哪里找 没开安全模式默认不为空的!!
- [求助]mysql字符+日期+4位流水号 这种数据怎么自动生成?
- sql语句优化问题
- 求一条条件插入语句
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
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>这样感觉速度很慢
select transdate,concat(floor((hour(transdate)-1)/3)*3+1,':00:00-',floor(((hour(transdate)-1)/3)*3+4)%24,':00:00') from d1