动态，使用MySQL中的where子句中的案例语句

发布于 2025-01-25 06:41:48 字数 868 浏览 3 评论 0 原文

我被卡住了一个动态，其中案例语句中的子句。

我需要的

当我使用它时，

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的日期。快照我的数据库包括昨天的数据，我有问题。

原文

I am stucked at a dynamic where clause inside case statement.

WHAT I NEED

When i used this

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;

The syntax is wrong but when instead it '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;

It is correct but i want to change dynamically.how can i do it.
I mean that
when i run the query include date of 1-4-2022 to 30-4-2022.
The snapshot my database include data of yesterday in the begin of month i have the issue.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

美男兮 2025-02-01 06:41:48

案例语句只能返回一个值，而不能返回范围。如果我正确地理解，只是开始日期的变化，因此我们只需要确定案例语句中的开始日期即可。如果结束日期也更改，我们将需要第二个案例语句。
但是，看起来一个简单的测试将返回相同的结果，

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＆gt; gt; fiddle 在这里

A case statement can only return a value and not a range. If I understand rightly it is only the start date which changes so we only need to decide the start date in the case statement. If the end date also changes we will need a second case statement.
However it looks like a simple test will return the same results

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;

Re-reading your explanation I think that you really want

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<>fiddle here

回复收藏 0 原文

~没有更多了~