如何讓 MySQL 在聚合選擇中迭代子查詢?
因為描述我想做的事情的詞彙可能如此不同,所以我沒有任何運氣弄清楚為什麼 MySQL 5.6 正在做它所做的事情。
考慮以下兩個簡單的查詢:
#1 select sum(amount) as amt, (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost from gas_log gl where date_format(gl.stamp,'%X')=2013; #2 select sum(amount) as amt, (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost from gas_log gl where date_format(gl.stamp,'%X')=2013 group by yearweek(gl.stamp,0);
第二個查詢與第一個查詢相同,使用簡單的 group_by 來獲取每週總計而不是每年總計。兩個查詢都有幾乎相同的解釋輸出:
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | PRIMARY | gl | ALL | NULL | NULL | NULL | NULL | 7428 | Using where; Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | gp | ALL | yw | NULL | NULL | NULL | 52 | Using where | +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | gl | ALL | NULL | NULL | NULL | NULL | 7428 | Using where | | 2 | DEPENDENT SUBQUERY | gp | ALL | yw | NULL | NULL | NULL | 52 | Using where | +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
gas_log
包含抽氣時間和抽氣量的時間戳。gas_prices
看起來像這樣:10:11:09> select * from gas_prices limit 2; +------------+--------+-------+--------+ | date | yw | gas | diesel | +------------+--------+-------+--------+ | 2013-01-07 | 201301 | 3.235 | 3.870 | | 2013-01-14 | 201302 | 3.265 | 3.834 | +------------+--------+-------+--------+
對於第一個查詢,MySQL 只執行一次子查詢,並使用該值(通過將檢索到的第一行
gas_log
與其對應的星期匹配找到gas_prices
)乘以記錄在氣體日誌中的所有加侖的總和,而在第二個查詢中它做我真正追求的事情:為 2013 年的 52 個分組周中的每一周執行子查詢,相應地匹配每週的汽油價格。使用
with rollup
ongroup by
提供了一個有趣的結果;它不使用時間範圍內的第一個 gas 價格,而是使用最後一個!因此,它仍然為超過一周的任何時間範圍提供不正確的總成本。有沒有辦法重寫查詢以使 MySQL 在給定的時間範圍內顯示一個總數,但仍將每一
gas_log
行與其對應的價格相匹配gas_prices
?
(sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost
是ypercube提供的答案。我是操作順序的受害者——MySQL 將抽取的加侖數相乘,然後將其與從 gas 價格子查詢中找到的第一個/最後一個結果相乘。正如邁克爾引用和指出的那樣:
“伺服器可以從每個組中自由選擇任何值,因此除非它們相同,否則選擇的值是不確定的。”
所以為了得到我想要的結果,每個條目的總和乘以那一周的 gas 價格,你只需將那個精確的表達式包裹在一個舒適的擁抱中
sum()
。