选择具有不同条件的两个和MySQL

Question

我有一个数据库表“条目”与员工的时间日志。

employee_id    |    hours    |    task_id    |    logged_at
---------------------------------------------------------------------
1              |    .5       |    1          |    1546342200 (jan 19)
2              |    .8       |    2          |    1547548200 (jan 19)
1              |    .2       |    3          |    1549022400 (feb 19)
1              |    .5       |    3          |    1549022400 (feb 19)
2              |    .9       |    1          |    1551625200 (mar 19)

我的目标是拉出一份报告，列出一年中每个月的计费和非计费小时数。可计费还是不可计费由任务ID决定。我有一个可计费的任务ID列表和一个非可计费的任务ID列表。

最终结果的目标

month    |    year    |    total   |    billable    |    nonbillable
-------------------------------------------------------------------
1        |    2019    |    1.3     |    .8          |    .5
2        |    2019    |    .7      |    .7          |    0
3        |    2019    |    .9      |    0           |    .9

这是基于任务2和3是可计费的，而任务1是不可计费的。

请注意，这是真实数据库的简化，它有大约25个不同的任务ID，偶尔会发生变化。

我能够得到大体的想法，但被困在基于task_id的计费小时和非计费小时的划分上。

这就是我所得到的：

SELECT 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at)), 
  SUM(hours)
FROM dpd_harvest_entries
GROUP BY 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at))

Answer 1

我假设您有一个包含列billable的tasks表，其中1代表可计费任务，0代表非计费任务。那么您的查询可能如下所示：

SELECT 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at)),
  SUM(hours) total,
  SUM(IF(t.billable,hours,0)) billable,
  SUM(IF(t.billable,0,hours)) nonbillable
FROM dpd_harvest_entries e
INNER JOIN tasks t
ON e.task_id = t.task_id
GROUP BY 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at))

如果您只使用逗号分隔ids，则可以将此查询转换为：

SELECT 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at)),
  SUM(hours) total,
  SUM(IF(task_id IN ({your id list}),hours,0)) billable,
  SUM(IF(task_id IN ({your id list}),0,hours)) nonbillable
FROM dpd_harvest_entries e
GROUP BY 
  MONTH(FROM_UNIXTIME(logged_at)), 
  YEAR(FROM_UNIXTIME(logged_at))

其中{your id list}应替换为ex：IN (1, 4, 55, 47)

为了将来的使用，我强烈建议使用ALTER TABLE dpd_harvest_entries MODIFY logged_at DATETIME，它将简化许多查询，并允许在任何地方消除FROM_UNIXTIME调用。

Answer 2

这应该行得通..

select a.month, a.year, (billable + non_billable) total, billable, non_billable
from (select month, year, sum(hours) billable
      from dpd_harvest_entries
      where task_id in (2, 3)
      group by month, year) a,
     (select month, year, sum(hours) non_billable
      from dpd_harvest_entries
      where task_id in (1))
      group by month, year) b
where a.month = b.month
and a.year = b.year

我假设您可以将时间戳转换为月和年

Answer 3

您需要两个子查询(可计费和不可计费)，在这两个查询中都有一个年-月列，然后在您的年-月列中将它们连接在一起，如下所示：

SELECT 
billable.year_month as year_month,
billable.sum_hours as billable_hours,
non_billable.sum_hours as non_billable_hours,
(billable.sum_hours + non_billable.sum_hours) as total_hours

FROM (SELECT 
DATE_FORMAT(FROM_UNIXTIME(logged_at), '%Y-%m') as year_month,
SUM(hours) as sum_hours
FROM dpd_harvest_entries
WHERE task_id IN (1,2,3)
GROUP BY
1) as billable,

(SELECT 
DATE_FORMAT(FROM_UNIXTIME(logged_at), '%Y-%m') as year_month,
SUM(hours) as sum_hours
FROM dpd_harvest_entries
WHERE task_id IN (4,5,6)
GROUP BY
1) as non_billable

LEFT JOIN billable on billable.year_month = non_billable.year_month

注意这里的联接类型；如果在一个月内没有计费或非计费小时数，这将跳过几个月。不确定在您的情况下需要哪种联接类型。