左外连接不会从我的左表中返回所有行?
我正在尝试使用以下查询获取每天打开的页面数.
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.
相关文章