mysql - 获取内部联接中特定实例的数量？

Question

我有三个表:用户、列表和任务。

list表有user_id作为外键。 tasks 表有 list_id 作为外键和 user_id 作为字段。

我正在使用以下 SQL 语句来显示用户列表和已完成任务的数量:

SELECT
    list.list_id,
    list_name,
    list_time_created,
    COUNT( task.task_id ) AS numberofTasks,
    COUNT( CASE WHEN task.task_completed=1 THEN 1 END) AS numberofCompletedTasks
FROM list
INNER JOIN task
    ON list.user_id = ':user_id' AND list.list_id = task.list_id
GROUP BY list_name

完整的表格设计如下:

列表

• list_id
• 名单名称
• user_id FK

任务

• 任务编号
• 任务名称
• 任务完成
• 用户编号
• list_id FK

它不起作用。我如何修改此语句以获取用户的所有列表(即使列表不包含任务)并获取任务数(总计和已完成)(如果有)？

Answer 1

您的部分问题是您只检查列表中的 user_id。您还需要确保在任务表中获得正确的 user_id。一种方法是在该条件下连接表，然后在 where 子句中检查特定用户。

其次，如果您想查看列表而不管它是否有任务，您应该使用外连接。如果这样做，我建议修改聚合函数以处理空值。尝试这样的事情:

SELECT 
  l.list_id, l.list_name, l.list_time_created, 
  COUNT(CASE WHEN t.task_id IS NULL THEN 0 ELSE 1 END) AS numberOfTasks,
  SUM(CASE WHEN t.task_completed IS NULL THEN 0 ELSE t.task_completed END) AS numberOfCompletedTasks
FROM list l
LEFT JOIN task t ON l.list_id = t.list_id AND l.user_id = t.user_id
WHERE l.user_id = 1
GROUP BY l.list_id;

这将选择您的列，计算 task_id 的所有非空实例，以及每个 list_id 的 task_completed 为 1 的所有实例。这将显示所有 list_id，因此如果它没有任务，我们将看到计数为 0。这里不需要按 user_id 进行额外分组，因为您正在查询特定用户。

由于 task_completed 为 0 或 1，使用 SUM 聚合函数将有效地为您提供已完成任务的计数。我使用支票对空实例求和为 0。

这在 SQL Fiddle 中解决了对我来说，如果它给您带来问题，请告诉我。

编辑

如果我可以评论您的表设计，我会建议从其中一个表中删除 user_id。没有必要同时拥有它。假设您从任务表中删除了 user_id。我可以从任务-> 列表中获取 user_id。如果我同时拥有它，它们可能会不一致。但是，这只是评论。我不知道您的整个情况，更改您的设计可能是不可能的，或者您有理由这样做。