mysql 去年同一天_MySQL去年同一天

我建議@ MartinJansen的答案 - MySQL的工作好了很多行方面比列明智的，尤其是當您只需要一個特定的星期來提取。

不過，如果你想讓它列明智的，或者如果你希望它超過一個特定的星期，你可以嘗試：

mysql> SELECT * from sales;

+------------+------+

| date | sold |

+------------+------+

| 2011-01-01 | 1 |

| 2011-01-02 | 10 |

| 2011-01-03 | 3 |

| 2012-01-01 | 10 |

| 2012-01-02 | 20 |

+------------+------+

SELECT CONCAT('week',WEEK(t.`date`)) as week,

SUM(IF(YEAR(t.`date`)=YEAR(DATE_ADD(NOW(), INTERVAL - 1 YEAR)),t.sold,0)) as `2011`,

SUM(IF(YEAR(t2.`date`)=YEAR(NOW()),t2.sold,0)) as `2012`

FROM sales t

LEFT JOIN sales t2

ON t.date = t2.date --could add WHERE WEEK(t.date)=... to restrict week

GROUP BY WEEK(t.date)

+-------+------+------+

| week | 2011 | 2012 |

+-------+------+------+

| week0 | 1 | 0 |

| week1 | 13 | 30 |

+-------+------+------+

(你必須要在周MySQL的確切定義讀了)。