我正在尝试使用以下查询获取每天打开的页面数.
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我得到的输出是这样:
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.
更多推荐
左外部联接不会返回我的左表中的所有行吗?
发布评论