时间戳

首页 > 解决方案 > 对表中没有记录的列显示零

mysql - 对表中没有记录的列显示零

问题描述

我有一个 MySQL 查询，它显示两个表中的数据，这些表由字段 project_id 连接。如果 projects 表中有记录但 project_tasks 表中没有记录，则不显示任何结果。

如何显示项目表的内容，但只为 project_tasks 表中不存在的任何字段显示零？

这是查询：

SELECT 
    projects.project_id,
    projects.project_name,
    projects.project_description,
    projects.creation_date,
    projects.deadline,
    sitelok.Name AS 'project_manager',
    COUNT(project_tasks.task_id) AS 'total_tasks',
    SUM(CASE
        WHEN project_tasks.status = 'Completed' THEN 1
        ELSE 0
    END) AS 'total_completed',
    SUM(CASE
        WHEN project_tasks.status = 'In Progress' THEN 1
        ELSE 0
    END) AS 'total_in_progress',
    SUM(CASE
        WHEN project_tasks.status = 'Not Started' THEN 1
        ELSE 0
    END) AS 'total_not_started',
    SUM(CASE
        WHEN project_tasks.status = 'Stuck' THEN 1
        ELSE 0
    END) AS 'total_stuck'
FROM
    projects,
    project_tasks,
    sitelok
WHERE
    projects.project_id = project_tasks.project_id
        AND projects.project_manager = sitelok.id
GROUP BY projects.project_id

标签： mysql

解决方案

而不是从两个表中进行选择，您需要对表进行左连接project_tasks。

在此处阅读有关左连接的更多信息。

您的查询将如下所示：

SELECT 
    projects.project_id,
    IFNULL(project_tasks.task_id, 0),
    ...
FROM projects
LEFT OUTER JOIN project_tasks ON project_tasks.project_id = projects.id

请注意我是如何使用IFNULL()的，因此如果 project_tasks 表中没有匹配的行，它将显示 0 。

推荐阅读