我被困住了，這項工作對我來說似乎太複雜了，我希望在這裡得到一些幫助！

所以這是我的桌子views：

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

我需要在數組中選擇過去 2 週每天的行數並進行比較（所以我需要比較本周和前一周的星期一）。

所以我嘗試了：

SELECT 'last_week' AS week, COUNT(*) AS rows 
   FROM `views`
   WHERE `time` >= CURDATE() - INTERVAL  6 DAY 
     AND `time` < CURDATE() + INTERVAL  1 DAY
 UNION ALL
   SELECT 'previous_week' AS week, COUNT(*) AS rows 
   FROM `views`
   WHERE `time` >= CURDATE() - INTERVAL 13 DAY
     AND `time` < CURDATE() - INTERVAL  6 DAY;

但它計算每週的行數，而不是單日。是的，我可以添加例如。AND DAYOFWEEK(查詢的時間) = 7;，但在這種情況下，我必須聲明它七次。

基本上我需要類似的東西：

+---------------+--------------------+
|          week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
|     last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+

其中 1 是星期一，7 是星期日。

初步日期數學

這裡有兩個你需要理解的表達方式

mysql> SET @this_past_monday = CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @prev_two_mondays = @this_monday - INTERVAL 2 WEEK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURDATE(),@this_past_monday,@prev_two_mondays;
+------------+---------------------+---------------------+
| CURDATE()  | @this_past_monday   | @prev_two_mondays   |
+------------+---------------------+---------------------+
| 2016-10-11 | 2016-10-10 00:00:00 | 2016-09-26 00:00:00 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

本答案的目標

您需要結合兩個查詢

• 根據這兩個星期一生成一個帶有日期的臨時表
• views根據這兩個星期一從表中檢索計數

查詢 #1

生成從去年 1 月 1 日到 1000 天后的所有日期，提取過去星期一之前 2 週內的所有日期

SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;

查詢 #1 已執行

mysql> SELECT dt FROM
   -> (SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
   -> (SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
   -> (SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
   -> (SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
   -> ORDER BY dt) A
   -> WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
   -> AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
+------------+
| dt         |
+------------+
| 2016-09-26 |
| 2016-09-27 |
| 2016-09-28 |
| 2016-09-29 |
| 2016-09-30 |
| 2016-10-01 |
| 2016-10-02 |
| 2016-10-03 |
| 2016-10-04 |
| 2016-10-05 |
| 2016-10-06 |
| 2016-10-07 |
| 2016-10-08 |
| 2016-10-09 |
+------------+
14 rows in set (0.00 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-10-11 |
+------------+
1 row in set (0.00 sec)

mysql>

查詢 #2

SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`);

組合

LEFT JOIN查詢 #1 和查詢 #2

SELECT A.dt,IFNULL(B.RowCount,0) RowsCounted FROM
(SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND) A
LEFT JOIN
(SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`)) B
USING (dt);

組合的原因/左加入

如果特定日期沒有行，您希望 Query #1 仍顯示日期但將 0 顯示為行數

試一試 ！！！

注意：我將把呈現數據的血腥細節留給你有能力的人

引用自：https://dba.stackexchange.com/questions/151964