Question

我有一个问题,

SELECT E.staffid AS staff
SUM(E.Frequency= Less ) AS lessCount, 
SUM(E.Frequency= More ) AS moreCount,  
FROM effort_frequency E 
INNER JOIN ost_staff S ON S.staff_id = E.staffid 
WHERE E.log_date BETWEEN  2012-05-01  AND  2012-05-23  
GROUP BY E.staffid

我得到了下面的桌子,

+-------+-----------+-----------+
| Staff | lessCount | moreCount |
+-------+-----------+-----------+
|  1    |    2      |     3     |
|  3    |    1      |     4     |
|  5    |    2      |     3     |
+-------+-----------+-----------+

您可以看到结果中缺少 2 & amp; 4 。在给定日期的 < code> effort_ danger 表格中, 将会有 < em> no 列 , 有 staff_ id 2 & amp; 4 。但我正在尝试加入 < code> ost_ staff 表格, 这样我就可以得到像下表一样的东西。 ost_ staff 将包含所有的工作人员 ID, 例如 1-5 。

+-------+-----------+-----------+
| Staff | lessCount | moreCount |
+-------+-----------+-----------+
|  1    |    2      |     3     |
|  2    |    0      |     0     |
|  3    |    1      |     4     |
|  4    |    0      |     0     |
|  5    |    2      |     3     |
+-------+-----------+-----------+

我尝试了外缘组合但它不工作, 我想我做错了某处。

Answer 1

将 Where 条款移至 ON 条款,并将 INNER JOIN 改为 right JOIN 。并将 GROUP 由 E.staffid 改为 GROUP 由 S.staff_id ;同样,将 SELECT E.staffid, 改为 SELECT s.staff_id :

SELECT S.staff_id AS staff

    COALESCE(SUM(E.Frequency= Less ),0) AS lessCount, 
    COALESCE(SUM(E.Frequency= More ),0) AS moreCount,  

FROM effort_frequency E 

RIGHT JOIN ost_staff S 
ON S.staff_id = E.staffid 
AND E.log_date BETWEEN  2012-05-01  AND  2012-05-23  

GROUP BY S.staff_id

如果您想要使用 LEFT JOIN ,请切换表格ost_staff和工作频率:

SELECT S.staff_id AS staff

    COALESCE(SUM(E.Frequency= Less ),0) AS lessCount, 
    COALESCE(SUM(E.Frequency= More ),0) AS moreCount,  

FROM ost_staff S 

LEFT JOIN effort_frequency E 
ON S.staff_id = E.staffid 
AND E.log_date BETWEEN  2012-05-01  AND  2012-05-23  

GROUP BY s.staff_id

您也可以使用 IFNULLLL 代替 COALESCE 。 IFNULLL 只对 MySQL 工作

Answer 2

您是否尝试过“ 强势@ a href=” “ http:// mysqljoin. com/joins/left-outer- join- tutory/” rel=“ 不跟随”?

SELECT E.staffid AS staff
       COALESCE(SUM(E.Frequency= Less ),0) AS lessCount, 
       COALESCE(SUM(E.Frequency= More ),0) AS moreCount,  
FROM effort_frequency E LEFT JOIN ost_staff S 
      ON S.staff_id = E.staffid 
WHERE E.log_date BETWEEN  2012-05-01  AND  2012-05-23  
GROUP BY E.staffid

< a href=" "http://mysqljoin.com/" rel="nofollow"\\\\ strong > 学习 SQL JOINS 的文章

添加了“坚固a href=”“http://www.w3schools.com/sql/sql_isnull.asp” rel=“nofollow”>COALESCE ,因为如果没有记录,它会返回“em>NULL 的价值。

友情链接