問題描述
我正在嘗試使用以下查詢獲取每天打開的頁面數.
I am trying to get the number of page opens on a per day basis using the following query.
SELECT day.days, COUNT(*) as opens
FROM day
LEFT OUTER JOIN tracking ON day.days = DAY(FROM_UNIXTIME(open_date))
WHERE tracking.open_id = 10
GROUP BY day.days
我得到的輸出是這樣的:
The output I get it is this:
days opens
1 9
9 2
問題是，在我的日期表中，我有一列包含數字 1 到 30 來表示一個月中的天數.我做了一個左外連接，我期待在天數列中顯示所有天數！
The thing is, in my day table, I have a single column that contains the number 1 to 30 to represent the days in a month. I did a left outer join and I am expecting to have all days show on the days column!
但我的查詢正在這樣做，為什么會這樣?
But my query is doing that, why might that be?
推薦答案
Nanne 的回答 解釋了為什么你沒有得到想要的結果(你的 WHERE 子句刪除了行)，但沒有說明如何修復它.
Nanne's answer given explains why you don't get the desired result (your WHERE clause removes rows), but not how to fix it.
解決方案是將 WHERE 改為 AND 使條件成為連接條件的一部分，而不是連接后應用的過濾器:
The solution is to change WHERE to AND so that the condition is part of the join condition, not a filter applied after the join:
SELECT day.days, COUNT(*) as opens
FROM day
LEFT OUTER JOIN tracking
ON day.days = DAY(FROM_UNIXTIME(open_date))
AND tracking.open_id = 10
GROUP BY day.days
現在左表中的所有行都將出現在結果中.
Now all rows in the left table will be present in the result.
這篇關于左外連接不會從我的左表中返回所有行?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！