每天分组填补空白

本文介绍了每天分组填补空白的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试根据本教程使用MySQL生成具有空白的每日销售报告. 为此，我有三个表:记录表，用户表和日历表

I am trying to generate a daily sales reports for a particular user based on this tutorial Using MySQL to generate daily sales reports with filled gaps. To do this, I have three tables, records table, user table and calendar table

records user calendar id id datefield user_id timestamp

下面的查询在特定日期没有可用的情况下，返回总计0和NULL作为user_id，这很不错:

The query below returns 0 as total and NULL as the user_id when data is not available for a particular day which is great:

SELECT calendar.datefield AS DATE, IFNULL(COUNT(records.id),0) AS total, records.user_id FROM records RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) WHERE ( calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW() ) GROUP BY DATE DESC

想法是为特定用户生成此报告，因此我将上述查询修改为以下内容:

The idea is to generate this report for a particular user so I modified the above query to what follows:

SELECT calendar.datefield AS DATE, IFNULL(COUNT(records.id),0) AS total, records.user_id FROM records RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) WHERE ( calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW() AND records.user_id = SOME_EXISTING_USER_ID ) GROUP BY DATE DESC

如果没有记录，但是在没有数据的任何特定日期返回0，则返回空结果.

This return an empty result when there is no record but the idea is to return 0 for any particular day which does not have data.

如何修改第一个查询以使其适合特定用户?

How can I modify the first query to work for a particular user?

推荐答案

哇.自从我在野外看到RIGHT JOIN以来已经有一段时间了！无论如何，尝试像这样将WHERE子句中的用户谓词添加到RIGHT JOIN中:

Wow. Been a while since I've seen a RIGHT JOIN in the wild! Anyway, try adding the user predicate from the WHERE clause into the RIGHT JOIN like this:

SELECT calendar.datefield AS DATE, IFNULL(COUNT(records.id),0) AS total, records.user_id FROM records RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) AND records.user_id = SOME_EXISTING_USER_ID WHERE ( calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW() ) GROUP BY DATE DESC;

对我来说，这是显式连接与隐式连接的最大好处之一.

For me this is one of the great benefits of explicit joins vs implicit joins...