mysql - SQL中基于datediff和curdate函数计算列值

Question

是否可以按如下方式计算以下字段:

如果 DateDiff 中有一个整数，则 LateFee 列将由 (DateDiff * 3) 填充如果 DateDiff 中没有值，即租金仍未支付，则当前到期的 LateFee 计算公式为 (Date_Due - 当前日期) * 3

我正在通过存储过程尝试此操作:

delimiter $$
create procedure calcLateFees()
begin 
select Rental_ID as "Rental ID", Platform_ID as "Platform ID",Title_ID as "Title ID",Date_Due as "Due Date",
Date_Returned as "Returned On", Datediff(CURDATE(), Date_Due) * 3 as "Late Fee Outstanding"
from tp_rental
where month (curdate() = month(now()) and CURDATE() > Date_Due;
end $$

call calcLateFees();

我可能会做错所有事情，所以任何帮助将不胜感激。预先感谢您。

Answer 1

if there is an integer in DayDiff then the LateFee column will be populated by (DayDiff * 3). If there is no value in DayDiff [...] then the LateFee that is due currently is calculated by (Date_Due - current date) * 3

我会将其翻译为以下更新查询:

update tp_rental
set LateFee = case
    when DayDiff is not null then DayDiff * 3
    else datediff(date_due, curdate()) * 3
end