我建議@ 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的確切定義讀了)。