问题:

is there a simpler way than writing:

select date_sub(curdate(), interval WEEKDAY(curdate()) day) as LastMonday
from dual

回答1:

If you're not using an ancient MySQL, you can wrap this in a stored function.

CREATE FUNCTION `LastMonday`() RETURNS DATETIME    
  RETURN DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) ;

and then call

select LastMonday() as LastMonday

Update:

If you're having performance problems, you can persist the value in a session variable. That way you can be sure that it will only be calculated once.

set @LastMonday=LastMonday();
select @Lastmonday;

(in this simple query it makes no difference of course...)

回答2:

SET @dateCurrent = CURDATE();
SET @dateCurrentDayInWeek = DAYOFWEEK(@dateCurrent) - 2;
SET @dateLastMonday = DATE_ADD(@dateCurrent, INTERVAL -@dateCurrentDayInWeek DAY);

SELECT @dateLastMonday;

回答3:

Try this:

-- Today is 05 April 2013

-- Get Last Monday from MySQL

SELECT DATE_FORMAT(LAST_DAY(NOW()) - ((7 + WEEKDAY(LAST_DAY(NOW())) - 7) % 7), '%Y-%m-%d') last_monday;

-- Output

last_monday  
-------------
2013-04-29