如果没有现有记录，则计算0

Question

我有一个有两个表的数据库。

1. 项目

​

+--------------+--------+
| project_name | Active |
+--------------+--------+
| A            | Yes    |
| B            | No     |
| C            | Yes    |
| D            | Yes    |
+--------------+--------+

​

1. 现场安全检查

​

+---------+-----------------+------------------------------+
| project | inspection_date |      Type_of_Inspection      |
+---------+-----------------+------------------------------+
| A       | 2019-08-20      | Supervisor Weekly Inspection |
| D       | 2019-08-20      | Supervisor Weekly Inspection |
| D       | 2019-08-21      | Supervisor Weekly Inspection |
| A       | 2019-08-22      | Supervisor Weekly Inspection |
+---------+-----------------+------------------------------+

​

project_name来自Projects表=来自form-site_的project。

定期将新行添加到表单site_safety_inspection中。在每周的基础上，我需要统计活动项目添加了多少行到form-site_site_inspection。如果在那一周没有一个活动项目添加任何行，我需要计数0。

到目前为止，我编写了一个查询，它对由活动项目添加的表单site_safety_inspection中的行进行计数。如果没有活动项目提交的行，则我缺少代码中显示0计数的部分。

SELECT COUNT(*), project

FROM form-site_safety_inspection

LEFT JOIN projects ON form-site_safety_inspection.project = Projects.project_name

WHERE form-site_safety_inspection.Type_of_Inspection= "Supervisor Weekly Inspection"

AND Projects.Active = "Yes"

AND year(inspection_date) = year(curdate())

AND month(inspection_date) = month(curdate())

AND week(inspection_date) = week(curdate())

GROUP BY form-site_safety_inspection.project

ORDER BY form-site_safety_inspection.project

使用上面的代码，我得到了结果：

​

+----------+---------+
| COUNT(*) | project |
+----------+---------+
|        2 | A       |
|        2 | D       |
+----------+---------+

​

我正在寻找以下结果：

​

+----------+---------+
| COUNT(*) | project |
+----------+---------+
|        2 | A       |
|        0 | C       |
|        2 | D       |
+----------+---------+

​

Answer 1

您的左联接方法是正确的，只不过日期限制需要出现在联接的ON子句中，而不是在WHERE子句中，因为您不希望它们过滤掉任何项目。注意，对Active的限制可以出现在WHERE子句中，因为您不希望看到任何不活动的项目。

SELECT
    p.project_name,
    COUNT(f.project) AS cnt
FROM Projects p
LEFT JOIN `form-site_safety_inspection` f
    ON p.project_name = f.project AND
    YEARWEEK(inspection_date, 1) = YEARWEEK(CURDATE(), 1)
WHERE
    p.Active = 'Yes'
GROUP BY
    p.project_name;

​

​

​

Demo

​