mysql - 在 WHERE 子句和 BETWEEN 运算符中使用 CASE 表达式

Question

我正在尝试根据以下条件从我的表中获取一些行:

SELECT * FROM MyTable WHERE Date BETWEEN 
CASE dayofweek(curdate())
when 1 then curdate() AND adddate(curdate(), interval 6 day)
when 2 then subdate(curdate(), interval 1 day) AND adddate(curdate(), interval 5 day)
when 3 then subdate(curdate(), interval 2 day) AND adddate(curdate(), interval 4 day)
when 4 then subdate(curdate(), interval 3 day) AND adddate(curdate(), interval 3 day)
when 5 then subdate(curdate(), interval 4 day) AND adddate(curdate(), interval 2 day)
when 6 then subdate(curdate(), interval 5 day) AND adddate(curdate(), interval 1 day)
when 7 then subdate(curdate(), interval 6 day) AND curdate()
END

但由于某些原因它不起作用。它给了我一个语法错误。我应该如何完成这样的事情？

Answer 1

试试这个，

SELECT * 
FROM MyTable 
WHERE  1 = 
    CASE dayofweek(curdate())
        when 1 then Date BETWEEN curdate() AND adddate(curdate(), interval 6 day)
        when 2 then Date BETWEEN subdate(curdate(), interval 1 day) AND adddate(curdate(), interval 5 day)
        when 3 then Date BETWEEN subdate(curdate(), interval 2 day) AND adddate(curdate(), interval 4 day)
        when 4 then Date BETWEEN subdate(curdate(), interval 3 day) AND adddate(curdate(), interval 3 day)
        when 5 then Date BETWEEN subdate(curdate(), interval 4 day) AND adddate(curdate(), interval 2 day)
        when 6 then Date BETWEEN subdate(curdate(), interval 5 day) AND adddate(curdate(), interval 1 day)
        when 7 then Date BETWEEN subdate(curdate(), interval 6 day) AND curdate()
    END

在这种情况下，CASE() 语句只会返回两个可能的值:1 和 0。