mysql - 内部连接、计数和分组以获得比率

Question

我希望对于知道自己正在使用 mysql 做什么的人来说，这将是一个简单的问题。

我现在有两张 table ； “任务”和“完成”。

任务:

taskID  |  task_name  |  desciption  |  deadline
1       |  task 1     |  do this     | 2012-08-11
2       |  task 2     |  do that     | 2012-10-11
3       |  task 3     |  do the other| 2012-12-31

完成:

taskID  |  userID  |  status
1       |  24      |  complete
1       |  72      |  complete
1       |  15      |  late
2       |  41      |  complete
2       |  86      |  incomplete
3       |  4       |  incomplete
3       |  19      |  incomplete

我正在尝试在一个查询中检索已完成(准时或迟到)每项已超过截止日期的任务的用户比例(如下所示):

taskID  |  task_name  |  description  |  deadline  |  completion_ratio
1       |  task 1     |  do this      | 2012-08-11 |  0.66666
2       |  task 2     |  do that      | 2012-10-11 |  0.5

任务 3 没有显示，因为截止日期尚未过去。

下面的代码为我提供了对两个表的一个很好的简单连接，并仅选择那些已超过截止日期的任务，但我想知道如何获得上面显示的结果。

SELECT task.taskID,task.task_name,task.description,task.deadline,completion.status
  FROM task 
  INNER JOIN completion ON task.taskID=completion.taskID 
  WHERE task.deadline>=CURDATE()

查询结果:

taskID  |  task_name  |  description  |  deadline  |  status
1       |  task 1     |  do this      | 2012-08-11 | complete
1       |  task 1     |  do this      | 2012-08-11 | complete
1       |  task 1     |  do this      | 2012-08-11 | late
2       |  task 2     |  do that      | 2012-10-11 | complete
2       |  task 2     |  do that      | 2012-10-11 | incomplete

如有任何帮助，我们将不胜感激。

Answer 1

试试这个:

SELECT
  t.taskID,
  t.task_name,
  t.desciption, 
  t.deadline,
  SUM(CASE WHEN c.status = 'complete' THEN 1 ELSE 0 END) * 1.0 / 
  COUNT(c.status) AS completion_ratio
FROM tasks t
INNER JOIN completion c ON t.taskID = c.taskID 
WHERE t.deadline >= CURDATE()
GROUP BY   t.taskID,
  t.task_name,
  t.desciption, 
  t.deadline;

SQL Fiddle Demo ^{注意:为了演示，我修改了 fiddle demo 中的日期。}

这会给你:

| TASKID | TASK_NAME | DESCIPTION |   DEADLINE | COMPLETION_RATIO |
-------------------------------------------------------------------
|      1 |    task 1 |    do this | 2012-12-30 |          0.66667 |
|      2 |    task 2 |    do that | 2012-12-31 |              0.5 |