mysql - 去年MySQL同日

除了关于一周的开始/结束的争论之外，如何获得从同一表中选择的查询以比较本周与去年同一周的数字？

每次尝试时，数字都会膨胀，因为它正在执行跨产品类型的事情。

就像是

SELECT
DATE(t1.`date`) AS 'Date', SUM(t1.`sold`), SUM(t2.`sold`)
FROM `table` AS t1
JOIN `table` AS t2 ON WEEK(t1.`date`) = WEEK(t2.`date`)
WHERE WEEK(t1.`date`) = WEEK(CURDATE()) AND YEAR(t1.`date`) = YEAR(CURDATE()) AND YEAR(t2.`date`) = YEAR(DATE_ADD(NOW(), INTERVAL - 1 YEAR))
GROUP BY DATE(t1.`date`)

最佳答案

接下来呢？

mysql> SELECT * FROM test;
+------------+------+
| date       | sold |
+------------+------+
| 2012-02-10 |  100 |
| 2012-02-10 |  200 |
| 2011-02-10 |   50 |
| 2011-02-10 |   25 |
+------------+------+
4 rows in set (0.00 sec)

mysql> SELECT
    ->     CONCAT(WEEK(date), "-", YEAR(date)), SUM(sold)
    -> FROM
    ->     test
    -> WHERE
    ->     WEEK(`date`) = WEEK(NOW())
    ->     AND (YEAR(`date`) = YEAR(NOW()) OR YEAR(`date`) = YEAR(DATE_ADD(NOW(), INTERVAL - 1 YEAR)))
    -> GROUP BY
    ->     YEAR(date), WEEK(date);
+-------------------------------------+-----------+
| CONCAT(WEEK(date), "-", YEAR(date)) | SUM(sold) |
+-------------------------------------+-----------+
| 6-2011                              |        75 |
| 6-2012                              |       300 |
+-------------------------------------+-----------+
2 rows in set (0.00 sec)

关于mysql - 去年MySQL同日，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9223335/