时间戳

首页 > 解决方案 > 左连接不获取所有记录

sql - 左连接不获取所有记录

问题描述

我正在尝试通过使用左连接获取结果从我的表（组、fiches、客户、目标、事实）中获取所有记录，如果用户 ID 不存在则获取 0（例如：没有分配给成员的目标）我不知道我是否有效解释，但这是我的查询：

select CONCAT(s.firstname, ' ', s.lastname) AS Membre, g.achievement AS Objectif, count(l.assigned) AS `num_l`, count(c.id_fiche) AS `num_c`,
(count(c.id_fiche)/count(l.assigned))*100 AS `perc`, SUM(f.total) AS `glob`, (g.achievement - SUM(f.total)) AS `rest`, (SUM(f.total)/g.achievement)*100 AS `taux` 
from `groups` s 
LEFT JOIN `fiches` l ON s.id_membre=l.assigned LEFT JOIN `clients` c ON l.id=c.id_fiche 
LEFT JOIN `goals` g ON s.id_membre=g.id_membre LEFT JOIN `factures` f ON s.id_membre=f.addedfrom 
WHERE l.dateassigned BETWEEN  '2020-08-01' AND '2020-08-31' AND f.status=2 
GROUP BY s.id_membre

我得到的结果是为其分配了目标的成员，但我需要的是所有成员，并且没有为其分配目标的成员在与目标相关的字段中获得 0。谢谢你。

标签： sqlleft-join

解决方案

LEFT JOIN很好。INNER JOIN您已选择通过添加WHERE子句将其变成一个。

使用 时，除第一个表之外的所有表的LEFT JOIN过滤条件都应在条件中。ON

select CONCAT(s.firstname, ' ', s.lastname) AS Membre, g.achievement AS Objectif, 
       count(l.assigned) AS `num_l`, count(c.id_fiche) AS `num_c`, 
       (count(c.id_fiche)/count(l.assigned))*100 AS `perc`, SUM(f.total) AS `glob`, (g.achievement - SUM(f.total)) AS `rest`,
      (SUM(f.total)/g.achievement)*100 AS `taux`
from `groups` s LEFT JOIN
     `fiches` l
     ON s.id_membre = l.assigned AND
        l.dateassigned BETWEEN '2020-08-01' AND '2020-08-31' LEFT JOIN
     `clients` c
     ON l.id = c.id_fiche LEFT JOIN
     `goals` g
     ON s.id_membre = g.id_membre LEFT JOIN
     `factures` f
     ON s.id_membre = f.addedfrom AND f.status=2
GROUP BY s.id_membre

推荐阅读