求一个sql语句

[jun573]

table:call
+---------------------+------------+
| starttime_f | calledid_f |
+---------------------+------------+
| 2005-09-09 09:55:25 | 6506556758 |
| 2005-09-07 08:55:25 | 6506556760 |
| 2005-09-08 10:55:25 | 6506556758 |
+---------------------+------------+

table:customer
+----------+----------+----------+----------+----------+----------+
| open1_f | close1_f | open2_f | close2_f | open3_f | close3_f |
+----------+----------+----------+----------+----------+----------+
| 09:00:00 | 23:00:00 | 10:00:00 | 23:00:00 | 08:00:00 | 23:00:00 |
+----------+----------+----------+----------+----------+----------+
select规则如下
选择表call中的记录，当表call 中starttime_f是星期一时，要满足时间在open1_f和close1_f之间;
是星期二时，要满足时间在open2_f和close2_f之间;
是星期三时，要满足时间在open3_f和close3_f之间;
以此类推。
能否实现，怎么实现。

[mathematician]

SELECT a.* from `call` as a,customer as b WHERE
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;

[mathematician]

上面写的是星期一、二、三的，其他可以按顺序自己加

0->星期一，，，6->星期日。

[hy2003fly]

我只能做到这样，我举求星期一的例子如下：
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来实现。