MySQL 设置为 0 如果空(总和大小写)不起作用

Question

我想统计每个区域所有员工的条件，哪个条件有两个参数，WELL和UNWELL。像这样，

conditions    |    area    |    count_conditions
Well           AREA1               1
UNWELL          AREA1               0
Well           AREA2               5
UNWELL          AREA2               1
...

这是迄今为止最接近的一次。

SELECT a.conditions, k.area, 
SUM(CASE WHEN a.conditions IS NOT NULL THEN 1 ELSE 0 END) AS count_conditions
FROM tb_attended a
INNER JOIN tb_employees k ON a.nrp = k.nrp
AND a.date = '2020-07-20'
GROUP BY k.area, a.conditions

我上面的代码运行得很好，但如果在某个区域没有UNWELL或well的条目，那么这种情况就不会出现。就像这样。

conditions    |    area    |    count_conditions
Well           AREA1               1
Well           AREA2               5
UNWELL          AREA2               1
...

这是我使用的示例数据，SQL Fiddle

有什么建议吗？谢谢

Answer 1

首先需要不同区域与不同条件的CROSS联接，然后需要表的LEFT联接：

SELECT t1.condition, t2.area, 
COUNT(k.nrp) AS count_conditions
FROM (SELECT DISTINCT `condition` FROM tb_attended) t1
CROSS JOIN (SELECT DISTINCT Area FROM tb_employees) t2
LEFT JOIN tb_attended a ON a.condition = t1.condition
LEFT JOIN tb_employees k ON k.area = t2.area AND a.nrp = k.nrp AND a.date = '2020-07-20'
GROUP BY t1.condition, t2.area
ORDER BY t2.area, t1.condition DESC 

请参阅演示。

Answer 2

使用左联接

SELECT a.conditions, k.area, 
SUM(CASE WHEN a.conditions IS NOT NULL THEN 1 ELSE 0 END) AS count_conditions
FROM tb_attended a
left JOIN tb_employees k
ON a.nrp = k.nrp
AND a.date = '2020-07-20'
GROUP BY k.area, a.conditions

Answer 3

您可以在没有冗余条件语句的情况下使用LEFT JOIN，使用SUM()聚合：

SELECT a.conditions, k.area, 
SUM(a.conditions IS NOT NULL) AS count_conditions
FROM tb_attended a
LEFT JOIN tb_employees k
ON a.nrp = k.nrp
AND a.date = '2020-07-20'
GROUP BY k.area, a.conditions

Answer 4

我想你想要一个left join:

SELECT a.conditions, k.area, COUNT(a.conditions) AS count_conditions
FROM tb_employees k LEFT JOIN
tb_attended a
ON a.nrp = k.nrp AND a.date = '2020-07-20'
GROUP BY k.area, a.conditions;

编辑：

我明白了。您希望获得所有行，即使是没有匹配项的行。使用CROSS JOIN生成所需区域和条件的组合。然后使用LEFT JOIN匹配现有数据：

SELECT c.condition, e.area, COUNT(a.nrp) AS count_conditions
FROM (SELECT DISTINCT a.condition 
FROM tb_attended a
) c CROSS JOIN
(SELECT DISTINCT area
FROM tb_employees
) e LEFT JOIN
tb_employees k
ON k.area = e.area LEFT JOIN
tb_attended a
ON a.nrp = k.nrp AND 
a.condition = c.condition AND
a.date = '2020-07-20'
GROUP BY c.condition, e.area;

这里有一个db<gt；不停摆弄