我只能做到这样,我举求星期一的例子如下:
mysql> select * from `call`;
+---------------------+------------+
| starttime_f | calledid_f |
+---------------------+------------+
| 2005-09-09 09:55:25 | 758 |
| 2005-09-07 08:55:25 | 760 |
| 2005-09-08 10:55:25 | 758 |
| 2005-09-10 07:55:25 | 761 |
| 2005-09-11 10:55:25 | 762 |
+---------------------+------------+
5 rows in set (0.00 sec)mysql> select `call`.* from `call`,customer where dayofweek(starttime_f)=1 and
-> hour(starttime_f)>=extract(hour from open1_f) and hour(starttime_f)<=23;
+---------------------+------------+
| starttime_f | calledid_f |
+---------------------+------------+
| 2005-09-11 10:55:25 | 762 |
+---------------------+------------+
1 row in set (0.00 sec)不知道为什么用extract(hour from close1_f)会得到一个空集。
其他可以通过union来实现。
mysql> select * from `call`;
+---------------------+------------+
| starttime_f | calledid_f |
+---------------------+------------+
| 2005-09-09 09:55:25 | 758 |
| 2005-09-07 08:55:25 | 760 |
| 2005-09-08 10:55:25 | 758 |
| 2005-09-10 07:55:25 | 761 |
| 2005-09-11 10:55:25 | 762 |
+---------------------+------------+
5 rows in set (0.00 sec)mysql> select `call`.* from `call`,customer where dayofweek(starttime_f)=1 and
-> hour(starttime_f)>=extract(hour from open1_f) and hour(starttime_f)<=23;
+---------------------+------------+
| starttime_f | calledid_f |
+---------------------+------------+
| 2005-09-11 10:55:25 | 762 |
+---------------------+------------+
1 row in set (0.00 sec)不知道为什么用extract(hour from close1_f)会得到一个空集。
其他可以通过union来实现。
CASE (WEEKDAY(starttime_f))
when 0 THEN RIGHT(starttime_f,8) BETWEEN b.open1_f and b.close1_f
when 1 THEN RIGHT(starttime_f,8) BETWEEN b.open2_f and b.close2_f
when 2 THEN RIGHT(starttime_f,8) BETWEEN b.open3_f and b.close3_f
END;