SQlite使用不同的 "on"语句两次加入同一个表

Question

我找不到我的问题的答案，我不知道我的查询是否正确，这可能是 SQLite 问题，请帮助我解决问题。

我的数据库中有两个表:

processTable {id}
taskTable {id, processId, amount, done}

存在多对一关系(一个进程可以分配多个任务)。 “amount”和“done”是提供任务进度信息的整数值。如果“done”>=“amount”，则任务完成。我需要查询数据库以获得类似的东西:

+---------+-----------+------------+
| process | tasksDone | tasksCount |
+---------+-----------+------------+
| 1       | 1         | 3          |
+---------+-----------+------------+
| 2       | 2         | 5          |
+---------+-----------+------------+

根据我的表中的数据:

processTable
+----+
| id |
+----+
| 1  |
+----+
| 2  |
+----+

tasksTable
+----+-----------+--------+------+
| id | processId | amount | done |
+----+-----------+--------+------+
| 1  | 1         | 10     | 10   | <- this task is done
+----+-----------+--------+------+
| 2  | 1         | 15     | 5    |
+----+-----------+--------+------+
| 3  | 1         | 80     | 5    |
+----+-----------+--------+------+
| 4  | 2         | 25     | 0    |
+----+-----------+--------+------+
| 5  | 2         | 60     | 60   | <- this task is done
+----+-----------+--------+------+
| 6  | 2         | 30     | 15   |
+----+-----------+--------+------+
| 7  | 2         | 40     | 40   | <- this task is done
+----+-----------+--------+------+
| 8  | 2         | 100    | 50   |
+----+-----------+--------+------+

所以，我写了这个查询:

SELECT processTable.id AS process, 
       COUNT(tasksTableDone.id) AS tasksDone, 
       COUNT(tasksTableAll.id) AS tasksCount 

FROM processTable

LEFT JOIN tasksTable AS tasksTableAll 
     ON   tasksTableAll.processId = processTable.id 

LEFT JOIN tasksTable AS tasksTableDone 
     ON   tasksTableDone.processId = processTable.id 
          AND
          tasksTableDone.done >= tasksTableDone.amount 

但我得到的是:

+---------+-----------+------------+
| process | tasksDone | tasksCount |
+---------+-----------+------------+
| 1       | 3         | 3          |
+---------+-----------+------------+
| 2       | 5         | 5          |
+---------+-----------+------------+

我试图一次只用一个连接运行查询，一切都运行良好。

仅使用第一次连接进行查询:

SELECT processTable.id AS process,  
       COUNT(tasksTableAll.id) AS tasksCount 

FROM processTable

LEFT JOIN tasksTable AS tasksTableAll 
     ON   tasksTableAll.processId = processTable.id 

Result:
+---------+------------+
| process | tasksCount |
+---------+------------+
| 1       | 3          |
+---------+------------+
| 2       | 5          |
+---------+------------+

仅使用第二个连接进行查询:

SELECT processTable.id AS process,  
       COUNT(tasksTableDone.id) AS tasksDone 

FROM processTable

LEFT JOIN tasksTable AS tasksTableDone 
     ON   tasksTableDone.processId = processTable.id 
          AND
          tasksTableDone.done >= tasksTableDone.amount 

Result:
+---------+-----------+
| process | tasksDone |
+---------+-----------+
| 1       | 1         |
+---------+-----------+
| 2       | 2         |
+---------+-----------+

如何在一个查询中使用这两个连接来获得正确的结果？我知道我可以使用另一个 SELECT 代替 JOIN，但我认为在性能方面它会更昂贵。

Answer 1

您可以实现 CASE带有聚合的语句:

版本使用 SUM()

SELECT p.id AS process,  
  sum(case when t.amount = t.done then 1 else 0 end) AS tasksDone,
  count(p.id) AS tasksCount
FROM processTable p
LEFT JOIN tasksTable t
  ON t.processId = p.id 
group by p.id

见 SQL Fiddle with Demo

版本使用 COUNT() :

SELECT p.id AS process,  
  count(case when t.amount = t.done then 1 else null end) AS tasksDone,
  count(p.id) AS tasksCount
FROM processTable p
LEFT JOIN tasksTable t
  ON t.processId = p.id 
group by p.id

见 SQL Fiddle with Demo

编辑，在您发表评论后，您可以将其包装在选择中以获得 progress :

select process,
  tasksDone,
  tasksCount,
  (tasksDone / tasksCount) progress
from
(
  SELECT p.id AS process,  
    count(case when t.amount = t.done then 1 else null end) AS tasksDone,
    count(p.id) AS tasksCount
  FROM processTable p
  LEFT JOIN tasksTable t
    ON t.processId = p.id 
  group by p.id
) src