java - SQL 查询从 MySQL 数据库表的日期期间检索数据，其中两个日期之一具有固定月份

Question

我正在编写一个可以跟踪我的财务状况的程序。在其中一个组件 (JLabel) 中，我想查看从 fixed_day_of_the_month 到今天花费的钱。逻辑是:

fixed_day_of_the_month = 10;
get current date of the month;
if (currentdayOfTheMonth > fixed_day_of_the_month){
getDates BETWEEN CURDATE() AND fixed_day_of_the_month;
} else if (currentdayOfTheMonth <= fixed_day_of_the_month){
getDates BETWEEN CURDATE() AND fixed_day_of_the_month of LAST month;
}

对可以完成这项工作的 sql 语句有什么想法吗？我正在努力解决最后一点问题，我必须获得上个月的 fixed_day_of_the_month。

Answer 1

解决方案可能是这样的:

SELECT 
*
FROM myTable, (select @fixed_day_of_the_month := 30) as a
WHERE 
IF(day(now()) > @fixed_day_of_the_month, 
 getDates BETWEEN CURDATE() AND fixed_day_of_the_month, 
  IF(day(now()) <= @fixed_day_of_the_month, 
   getDates BETWEEN CURDATE() AND fixed_day_of_the_month of LAST month
 ,0)
)

可变日期可以通过这样的方式解决:

select 
    concat(year(now()),"-",month(now()),"-",18),
    DATE_SUB(concat(year(now()),"-",month(now()),"-",18), INTERVAL 1 MONTH)
;