mysql - 连接mysql中的两列

Question

我想将表 b 中的数据添加到表 a 中，但不幸的是，完全外连接在 mysql 中不起作用。我也尝试过 union 但它抛出错误，因为我的语句有 group by 和 order by 关键字

SELECT COUNT( ReviewedBy ) AS TotalReviews, OrganizationId, SUM( Rating ) AS TotalStars, COUNT( Rating ) AS TotalRatings, (
       SUM( Rating ) / COUNT( Rating )
          ) AS AverageRating
        FROM  `tbl_reviews` 
      WHERE ReviewType =  'shopper'
      AND ReviewFor =  'org'
       AND OrganizationId
               IN (

           SELECT OrganizationId
         FROM tbl_organizations
         WHERE CategoryID =79
          )
        GROUP BY OrganizationId
        ORDER BY AverageRating DESC 

This is what i'm getting from the above statement

我想在结果中获取organizationId 21数据，但我没有得到结果，因为它不存在于“tbl_review”表中 click here to see the table b

如何获得想要的结果？

Answer 1

您不需要 FULL 连接，而是需要 LEFT 连接:

SELECT COUNT( ReviewedBy ) AS TotalReviews, o.OrganizationId, 
   SUM( Rating ) AS TotalStars, COUNT( Rating ) AS TotalRatings, 
   (SUM( Rating ) / COUNT( Rating )) AS AverageRating
FROM tbl_organizations AS o
LEFT JOIN `tbl_reviews` AS r
  ON o.OrganizationId = r.OrganizationId
 AND ReviewType =  'shopper' -- conditions on inner table 
 AND ReviewFor =  'org'      -- must be moved to ON
WHERE CategoryID =79
GROUP BY o.OrganizationId
ORDER BY AverageRating DESC 

为什么不使用AVG而不是SUM/COUNT？