左外部联接不会返回我的左表中的所有行吗?

本文介绍了左外部联接不会返回我的左表中的所有行吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用以下查询获取每天打开的页面数.

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.