如何排除MYSQL中的周末和比较当前日期

Question

我正在比较数据与表中的当前日期-1和-2日期。样本数据：

Name  cnt  Dates
Mills 20    2022-07-15
Mills 15    2022-07-14
Mills 10    2022-07-13

我编写了查询，其中比较了当前日期-1和当前日期-2。

SELECT Name,Date,`T-2`,`T-1`,Diff
    FROM (
SELECT  T.Name  ,T.Date,T.`T-1`,TT.`T-2`,TT.`T-2` - T.`T-1`  AS Diff FROM 
(select  Name, Date, cnt AS 'T-1' from Acct where date = curdate() - 1 )T
INNER JOIN 
(select  Name, Date, cnt AS 'T-2' from Acct where date = curdate() - 2)TT
ON T.Name = TT.Name )T

现在，当我执行这个查询当前日期(2022-07-18)时，它取-1作为2022-07-17，-2作为2022-07-16。我想把周末排除在外，把周末和星期五、星期四进行比较。

Out :

Name   T-1   T-2  DIFF 
Mills   20   15    5

Answer 1

将curdate() - 1替换为条件表达式curdate() - case weekday(curdate()) when 0 then 3 when 6 then 2 else 1 end。类似于curdate() - 2

SELECT Name,Date,`T-2`,`T-1`,Diff
    FROM (
SELECT  T.Name  ,T.Date,T.`T-1`,TT.`T-2`,TT.`T-2` - T.`T-1`  AS Diff FROM 
(select  Name, Date, cnt AS 'T-1' 
 from Acct 
 where date = curdate() - case weekday(curdate()) when 0 then 3 when 6 then 2 else 1 end) T
INNER JOIN 
(select  Name, Date, cnt AS 'T-2' 
 from Acct 
 where date = curdate() - case weekday(curdate()) when 0 then 4 when 1 then 4 when 6 then 3 else 2 end) TT
ON T.Name = TT.Name ) T

工作日()

Answer 2

SET @zi = curdate();
SET @x =   DAYOFWEEK(@zi);
SELECT @x;

case when  @x = 2 then  date = curdate() - 3 else  date = curdate() - 1 end