我正在嘗試為過去 7 天內的每個日期生成 7 行，并加入transactions表中的查詢。目的是有一個包含每個日期的表格，以及從第一個條目到該日期的quantity列的累計總數transactions：
| date | stockOnDate |
|---------------|---------------|
| 2021-10-15 | 10 |
| 2021-10-16 | 3 |
| 2021-10-17 | 0 |
| 2021-10-18 | 9 |
| 2021-10-19 | 15 |
| 2021-10-20 | 15 |
| 2021-10-21 | 15 |
我可以獲得日期串列，并且可以加入，但無法過濾嵌套查詢：
SELECT v.*, t.*
FROM ( SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 t2*100 t1*10 t0)) AS `date`
FROM (SELECT 0 t0 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) t0,
(SELECT 0 t1 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) t1,
(SELECT 0 t2 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) t2,
(SELECT 0 t3 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) t3 ) AS v
LEFT JOIN (SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate
FROM `transactions`
WHERE tDate <= v.`date`) AS t ON t.tDate = v.`date`
WHERE v.`date` >= DATE_SUB(NOW(),INTERVAL 7 DAY) AND v.`date` <= DATE(NOW())
但我收到以下錯誤：
#1054 - Unknown column 'tDate' in 'where clause'
如果我更換WHERE tDate <= v.date與WHERE DATE(timestamp) <= v.date我得到v.date同樣的錯誤-我似乎無法訪問父表的值。
我對 MySQL 不是很好，但似乎找不到解決方案，我哪里出錯了？
ProGuru 的解決方案
感謝ProGuru在下面的回答，下面的查詢按預期作業（使用<=而不是=在JOINwas鍵中）
SELECT b.date, SUM(a.quantity) AS stockOnDate
FROM (
SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 6 DAY), t3*1000 t2*100 t1*10 t0)) AS `date`
FROM (SELECT 0 t0 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) t0,
(SELECT 0 t1 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) t1,
(SELECT 0 t2 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) t2,
(SELECT 0 t3 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) t3
) b
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
AND a.organisationId = 1
GROUP BY b.date
ORDER BY b.date ASC
I can also change the GROUP BY to GROUP BY a.itemID, b.date to get the stock level on the given date for each itemId.
uj5u.com熱心網友回復：
要獲得數量的累計總和，日期連接需要使用 <=
修訂后的 SQL
SELECT b.date, SUM(COALESCE(a.quantity, 0))
FROM (
SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 t2*100 t1*10 t0)) AS `date`
FROM (SELECT 0 t0 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) t0,
(SELECT 0 t1 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) t1,
(SELECT 0 t2 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) t2,
(SELECT 0 t3 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) t3
) b
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
GROUP BY b.date
ORDER BY b.date ASC
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=34f78e3d7c9225727ac2e728588759e2
uj5u.com熱心網友回復：
要在 where 子句中使用別名，您必須將查詢封裝在另一個選擇中。
例子 ：
SELECT * FROM
(SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate FROM `transactions`) seb
WHERE seb.tDate <= v.`date`
更新試試這個：
SELECT v.*, t.*
FROM ( SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 t2*100 t1*10 t0)) AS `date`
FROM (SELECT 0 t0 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) t0,
(SELECT 0 t1 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) t1,
(SELECT 0 t2 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) t2,
(SELECT 0 t3 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) t3 ) AS v
LEFT JOIN (SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate FROM transactions) AS t ON t.tDate = v.`date`
WHERE t.tDate <= v.`date` and v.`date` >= DATE_SUB(NOW(),INTERVAL 7 DAY) AND v.`date` <= DATE(NOW())
這里可以使用 tDate
uj5u.com熱心網友回復：
首先，您需要了解在此子查詢中
(SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate
FROM `transactions`
WHERE tDate <= v.`date`) AS t
沒有任何定義為date甚至別名v。您基本上是在告訴查詢v.date在子查詢中不存在時進行查找。
我認為這就是你要找的：
SELECT dt, IFNULL(SUM(quantity),0) AS stockOnDate
FROM (SELECT STR_TO_DATE(CONCAT(LEFT(NOW(),7),LPAD(seq,2,0)), '%Y-%m%d') dt
FROM seq_1_to_31) v
LEFT JOIN `transactions` t ON dt=DATE(timestamp)
WHERE dt >= (SELECT MIN(DATE(timestamp)) FROM `transactions`)
AND dt <= (SELECT MAX(DATE(timestamp)) FROM `transactions`)
GROUP BY dt;
這里我使用 MariaDB 的內置序列引擎來生成天數；與當前的年月組合連接，然后用于STR_TO_DATE使其可識別為正確的日期格式。生成這些的查詢在 subquery 中v。
第二個選項是使用遞回公用表運算式根據表中現有的日期資料生成日期范圍。這是查詢：
WITH RECURSIVE cte AS (
SELECT MIN(DATE(timestamp)) mndt, MAX(DATE(timestamp)) mxdt FROM `transactions`
UNION ALL
SELECT mndt INTERVAL 1 DAY, mxdt FROM cte WHERE mndt INTERVAL 1 DAY <= mxdt)
SELECT mndt, IFNULL(SUM(quantity),0) AS stockOnDate
FROM cte
LEFT JOIN `transactions` t ON mndt=DATE(timestamp)
GROUP BY mndt;
上面的兩個查詢都不需要包含生成日期范圍的長子查詢。這是較新的 MySQL 和 MariaDB 版本的好處。
演示小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/330814.html