我被case语句中的where子句卡住了。
WHAT I NEED
当我使用这个
SELECT col1,col2,col3
FROM Recharge r INNER join ft f ON f.date=r.date
WHERE f.Date LIKE
CASE WHEN f.Date BETWEEN (last_day(curdate() - interval 1 month) + interval 1 day) AND last_day(curdate())
THEN f.Date ELSE f.date between subdate(curdate(),interval 1 month) and (last_day(curdate() - interval 1 month)) END
ORDER BY f.id desc;
语法错误,但当它改为'2022-04%'时。
SELECT col1,col2,col3
FROM Recharge r INNER join ft f ON f.date=r.date
WHERE f.Date LIKE
CASE WHEN f.Date BETWEEN (last_day(curdate() - interval 1 month) + interval 1 day) AND last_day(curdate())
THEN f.Date ELSE '2022-04%' END
ORDER BY f.id desc;
这是正确的,但我想动态地改变。我怎么能做到呢?我是说当我运行查询包括日期1-4-2022到30-4-2022。快照我的数据库包含昨天的数据在月初我有问题。
case语句只能返回一个值而不能返回一个范围。如果我理解正确的话,只是开始日期发生了变化,所以我们只需要在case语句中决定开始日期。如果结束日期也改变了,我们将需要第二个case语句。然而,看起来像一个简单的测试将返回相同的结果
SELECT col1,col2,col3
FROM Recharge r INNER join ft f ON f.date=r.date
WHERE f.Date BETWEEN
curdate() - interval 1 month
AND
last_day(curdate())
ORDER BY f.id desc;
重读你的解释,我认为你真的想要
SELECT col1,col2,col3
FROM Recharge r INNER join ft f ON f.date=r.date
WHERE month(f.date) = month(curdate() - interval 1 day)
AND year(f.date) = year(curdate() - interval 1 day )
ORDER BY f.id desc;
db<此处小提琴>此处小提琴>