mysql - 总结每周收入，包括空记录

Question

我正在尝试总结时间管理系统(PHP/MySQL)的每周收入，但是这个 SQL 查询遇到了很多麻烦。基本上，我需要返回过去 8 周的收入总额，包括没有收入的几周的记录，但是当我在 WHERE 子句中添加任何内容以将其范围缩小到特定类型的任务时，我无法完成这项工作。该查询涉及到三个表:

tbltask 存储有关任务的信息，包括记录任务的日期、执行任务的用户、应计费的时间以及任务是否可计费(有些任务不可计费，应从收入中排除)计算)...

task_id   task_name        time_est   billable   date_logged   user_id
----------------------------------------------------------------------
223       some task        120        0          2014-12-19    1
224       a billable task  45         1          2014-12-19    2
225       also billable    90         1          2014-12-20    1

tbluser存储用户信息，所以我需要加入它才能获得每小时的工资......

user_id   payrate   
--------------------
1         50
2         75          

日历只是一个具有广泛日期范围的表格，以便我可以连接它并生成没有记录的日期的结果。

datefield 
--------------------
2013-01-01
2013-01-02
2013-01-03
[...]
2025-12-31

下面是我迄今为止所掌握的数据，为我提供了相关日期之前 8 周内每个人每周(从星期一开始)的总收入。这似乎按预期工作，但计算所有任务而不仅仅是计费任务。如果这几周内没有记录任何任务，我会得到返回的记录，其中 Total_earned 为 0，这很重要，因为即使没有记录时间，我也需要过去 8 周的记录。

SELECT FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) AS first_day, 
SUM( IFNULL( time_est /60 * payrate, 0 ) ) AS total_earned
FROM calendar
LEFT JOIN tbltask ON tbltask.date_logged = calendar.datefield
LEFT JOIN tbluser ON tbltask.user_id = tbluser .user_id
WHERE datefield <=  '2014-12-26'
GROUP BY FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) 
ORDER BY FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) DESC 
LIMIT 8

但是，我只需要添加可计费任务的收入 (bilable=1)。添加此内容后，我将不再获得没有日志的几周，因此返回的记录中缺少几周。

SELECT FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) AS first_day, 
SUM( IFNULL( time_est /60 * payrate, 0 ) ) AS total_earned
FROM calendar
LEFT JOIN tbltask ON tbltask.date_logged = calendar.datefield
LEFT JOIN tbluser ON tbltask.user_id = tbluser .user_id
WHERE datefield <=  '2014-12-26' AND billable = 1 
GROUP BY FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) 
ORDER BY FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) DESC 
LIMIT 8

我明白为什么这个结果是有意义的(因为在那几周内没有完成 billable=1 任务，所以没有返回记录)，但我一生都无法弄清楚如何重写查询来获取我想要的是。我还想编写查询来获取特定用户的收入而不是所有用户的总收入(user_id=1)，但这当然给了我同样的问题。我想我可能需要使用子查询？

有人能指出我正确的方向吗？

<小时/>

解决方案:

如果其他人最终遇到类似的问题，我使用 terary 的 IF() 建议将 billable=1 和 user_id=1 逻辑移到 SUM 计算中，而不是将其放在 WHERE 子句中。这解决了我的问题，因为它返回所有空周，总收入为 0，而不是跳过没有记录的周。我确信还有其他方法可以做到这一点，但这确实有效。这是生成的查询:

SELECT FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) AS first_day, 
SUM(IF(billable=1 AND user_id=1, time_est /60 * payrate, 0)) AS total_earned
FROM calendar
LEFT JOIN tbltask ON tbltask.date_logged = calendar.datefield
LEFT JOIN tbluser ON tbltask.user_id = tbluser .user_id
WHERE datefield <=  '2014-12-26' AND billable = 1 
GROUP BY FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) 
ORDER BY FROM_DAYS(TO_DAYS(datefield) - MOD(TO_DAYS(datefield)-2, 7)) DESC 
LIMIT 8

Answer 1

请原谅我没有创建运行查询的表。所以我无法调试你所拥有的。

但是，A)您可以创建 tbltasks.wkyear ，这将消除对其中一个表的需求。这可以通过触发器插入时存储(也许是默认值？)。或者直接写下来。

B)好吧，我想我不确定你的目标？我想我现在已经有了一半的线索。

我认为 SELECT IF(billiable=1,50,0) 是你的 friend 。 http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek 选择 YEARWEEK('1987-01-01'); -> 198653