左外连接不返回左表中的所有行？

发布于 2024-10-12 17:23:01 字数 382 浏览 4 评论 0原文

我试图使用以下查询获取每天打开的页面数。

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 来表示一个月中的天数。我做了一个左外连接，我希望所有的天都显示在天数列上！

但我的查询是这样做的，为什么会这样呢？

原文

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

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾挽 2024-10-19 17:23:02

条件位于 WHERE 子句中。连接表后，将评估 WHERE 条件以过滤掉所有符合条件的内容。因此，任何不匹配 tracking.open_id = 10 的内容都会被丢弃。

如果要在连接两个表时应用此条件，更好的方法是将其与 ON 子句（即连接条件）一起使用，而不是整个数据集条件。

回复收藏 0 原文

才能让你更想念 2024-10-19 17:23:01

南妮的回答给定解释了为什么您没有得到所需的结果（您的 WHERE 子句删除了行），但没有解释如何修复它。

解决方案是将 WHERE 更改为 AND，以便条件成为联接条件的一部分，而不是联接后应用的过滤器：

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

现在左表中的所有行都将出现在结果中。

Nanne's answer given explains why you don't get the desired result (your WHERE clause removes rows), but not how to fix it.

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.

回复收藏 0 原文