Mysql count group by 和 left join 未按预期工作

发布于 2024-12-04 19:54:03 字数 560 浏览 5 评论 0原文

以下是点击跟踪链接的简单查询。我看了很多其他帖子，但我还是摸不着头脑。我无法让此查询工作，以便显示日历表中的所有行（每行一个日历日）：

SELECT DATE_FORMAT(calendar_date, '%a %D') AS calendar_date, 
count( tracker_id ) as clicks
FROM calendar
LEFT JOIN offer_tracker USING(calendar_id)
WHERE 
  calendar_month = Month(CURDATE()) AND 
  calendar_year = Year(CURDATE()) AND ( offer_id = 4 OR offer_id IS NULL )
GROUP BY calendar_date;

它几乎就在那里，但并非日历表中的所有行都返回，即没有星期五 2 日、星期二 6 日、星期三 7 日等：

result set

有人对我哪里出错有任何想法吗？我应该使用子查询吗？

原文

Below is a simple query for a click tracker. I've had a look at a lot of other posts and I'm scratching my head. I cannot get this query to work so that all rows from the calendar table (one calendar day per row) are displayed:

SELECT DATE_FORMAT(calendar_date, '%a %D') AS calendar_date, 
count( tracker_id ) as clicks
FROM calendar
LEFT JOIN offer_tracker USING(calendar_id)
WHERE 
  calendar_month = Month(CURDATE()) AND 
  calendar_year = Year(CURDATE()) AND ( offer_id = 4 OR offer_id IS NULL )
GROUP BY calendar_date;

It's nearly there but not all rows in the calendar table are returned i.e. there is no Fri 2nd, Tue 6th, Wed 7th etc:

result set

Does anyone have any ideas on where I'm going wrong? Should I be using a subquery?

分享到QQ

分享到微博

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

发布评论

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

等风来 2024-12-11 19:54:03

我猜 offer_id 来自 offer_tracker 表。当您有（左）外部联接，并且在 WHERE 条件中使用右表中的字段（例如您的 offer_id = 4）时，联接实际上被取消并给出与内连接相同的结果。

取消取消的尝试（offer_id = 4 OR Offer_id IS NULL）无法按您的预期进行。 offer_tracker 中带有 offer_id <> 的任何行4 已经通过了 LEFT JOIN，但由于 WHERE 条件而被删除。因此，如果该日期的 offer_id 行与 4 不同，则结果中不会出现 Friday 2nd 行。

将 offer_id = 4 检查移至 LEFT JOIN，而是：

SELECT DATE_FORMAT(calendar_date, '%a %D') AS calendar_date
     , count( tracker_id ) as clicks
FROM calendar
  LEFT JOIN offer_tracker 
    ON  offer_tracker.calendar_id = calendar.calendar_id
    AND offer_id = 4
WHERE calendar_month = Month(CURDATE())
  AND calendar_year = Year(CURDATE()) 
GROUP BY calendar_date

I guess the offer_id is from the offer_tracker table. When you have an (left) outer join, and you use a field from the right table in a WHERE condition (like your offer_id = 4), the join is actually cancelled and gives same results as an inner join.

The attempt to lift the cancellation (offer_id = 4 OR offer_id IS NULL) does not work as you expect. Any row from offer_tracker with offer_id <> 4 has already passed the LEFT JOIN but is removed because of the WHERE condition. So, no row with Friday 2nd will appear in the results if there is a row with offer_id different than 4 for this date.

Move the offer_id = 4 check to the LEFT JOIN, instead:

SELECT DATE_FORMAT(calendar_date, '%a %D') AS calendar_date
     , count( tracker_id ) as clicks
FROM calendar
  LEFT JOIN offer_tracker 
    ON  offer_tracker.calendar_id = calendar.calendar_id
    AND offer_id = 4
WHERE calendar_month = Month(CURDATE())
  AND calendar_year = Year(CURDATE()) 
GROUP BY calendar_date

回复收藏 0 原文

~没有更多了~