汇总每周收入，包括空记录

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 周每周（从星期一开始）每个人的总收入。这似乎按预期工作，但计算所有任务，而不仅仅是可计费任务。如果这些周中的任何一周都没有记录任务，我会得到以 0 作为total_earned返回的记录，这很重要，因为即使没有记录时间，我也需要过去 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

但是，我只需要将可计费任务的收入相加（计费=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（） 建议在 SUM 计算中移动计费=1 和 user_id=1 逻辑，而不是将其放在 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（计费=1，50，0）是你的朋友。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 选择年份周（'1987-01-01'）; -> 198653