mysql - 从mysql中的给定日期减去天数

Question

事假为10天。我需要从事假总额中减去剩余的假期，即 10 天

result i have

   SELECT id, firstname,lastname,join_date, completion_date,title, leave_from,leave_to, Given_leave_Days,Taken_Leave_Days,Remaining_Leave_Days  FROM (SELECT T1.id,T1.firstname,T1.lastname,T1.join_date,T1.completion_date,T0.title,T2.leave_from,T2.leave_to,T2.user_id,T0.Leave_Days as Given_leave_Days,SUM(DATEDIFF(T2.leave_to,T2.leave_from)+1) AS Taken_Leave_Days ,T0.Leave_Days - SUM(DATEDIFF(T2.leave_to,T2.leave_from)+1) as 'Remaining_Leave_Days' FROM users_leave_request T2 INNER JOIN users T1 ON T2.user_id = T1.id INNER JOIN leave_type T0 ON T2.leave_type_id = T0.id GROUP BY T1.id, T1.firstname, T1.lastname, T1.join_date,T1.completion_date,T0.title,T2.leave_from,T2.leave_to,Given_leave_Days order by firstname) A

我必须在前端显示结果。

result in frontend

Answer 1

一般答案是

select date_sub(now(), interval 10 day)

您可以替换 now()对于表中的任何日期，您可以更改 day对于 month , year等

我的示例的结果是今天 10 天前的日期。例如，如果您只想过滤 10 天之前的日期，您可以这样做:

select * from mytable t where t.mydate < date_sub(now(), interval 10 day)