mysql - 按天分组，填补空白

Question

我正在尝试根据本教程为特定用户生成每日销售报告 Using MySQL to generate daily sales reports with filled gaps .为此，我有三个表，记录表、用户表和日历表

records     user      calendar
id          id        datefield
user_id     
timestamp

当特定日期的数据不可用时，下面的查询返回 0 作为总数，返回 NULL 作为 user_id，这很好:

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

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

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。

如何修改第一个查询以适用于特定用户？

Answer 1

哇。自从我在野外看到 RIGHT JOIN 已经有一段时间了!无论如何，尝试将 WHERE 子句中的用户谓词添加到 RIGHT JOIN 中，如下所示:

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;

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