我正在尝试根据本教程为特定用户生成每日销售报告使用MySQL生成填补空白的每日销售报告.为此,我有三个表,记录表,用户表和日历表
records user calendar id id datefield user_id timestamp
下面的查询返回0作为total,并且当数据在特定日期不可用时返回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.
如何修改第一个查询以便为特定用户工作?