php - 如何从当前日期计算最后四个星期日的日期

Question

我们如何在 mysql 中获取相对于当前日期的最后四个星期日的日期？谢谢，

假设今天的日期是 12/14/2012，那么 sql 查询的输出应该如下所示

1st Sunday  | 2nd Sunday  |  3rd Sunday  |  4rth Sunday11/11/2012    18/11/2012     25/11/2012     09/12/2012

UPDATE:

With PHP I did this:

    $date_lastsunday = strtotime("last Sunday");
$w1_sunday = $date_lastsunday - 7 * 24 * 3600;
$w2_sunday = $date_lastsunday - 14 * 24 * 3600;
$w3_sunday = $date_lastsunday - 21 * 24 * 3600;
$w4_sunday = $date_lastsunday - 28 * 24 * 3600;

想知道如何用 mysql 完成...

Answer 1

如果你希望它们在每一行中

SELECT Curdate() - INTERVAL (Weekday(Curdate())+1) day AS `Sunday` 
UNION 
SELECT Curdate() - INTERVAL (Weekday(Curdate())+1+7*1) day 
UNION 
SELECT Curdate() - INTERVAL (Weekday(Curdate())+1+7*2) day 
UNION 
SELECT Curdate() - INTERVAL (Weekday(Curdate())+1+7*3) day 

如果您希望它们在列中，请将 UNION SELECT 替换为 ,。像这样

SELECT Curdate() - INTERVAL (Weekday(Curdate())+1) day `1st Sunday`, 
       Curdate() - INTERVAL (Weekday(Curdate())+1+7*1) day `2nd Sunday`, 
       Curdate() - INTERVAL (Weekday(Curdate())+1+7*2) day `3rd Sunday`, 
       Curdate() - INTERVAL (Weekday(Curdate())+1+7*3) day `4th Sunday` 

CPU 密集程度较低的方法，

SET @OFS=Weekday(Curdate())+1;
SET @CD=curdate();
sELECT @CD - INTERVAL (@OFS) day `1st Sunday`, 
       @CD - INTERVAL (@OFS+7*1) day `2nd Sunday`, 
       @CD - INTERVAL (@OFS+7*2) day `3rd Sunday`, 
       @CD - INTERVAL (@OFS+7*3) day `4th Sunday`