mysql - 与供应商表和供应商评级表进行左连接时，仅显示一条记录

Question

这个问题已经有答案了:

奥 git _a (1 个回答)

已关闭 4 年前。

我有一个供应商表和一个供应商评级表。我正在尝试创建一个 View ，在左侧显示所有供应商，在右侧显示他们的总体评级。我希望供应商能够出现，即使他们还没有被评级。我使用的是mysql 5.7。这是我的查询:

SELECT
vendors.ID,
vendors.Vendor,
ROUND(((AVG(`Cost_Rating`) + AVG(`Documentation_Rating`) + AVG(`Safety_Rating`) + AVG(`Equipment_Rating`) + AVG(`Performance_Rating`) + AVG(`Promptness_Rating`) + AVG(`Communication_Rating`))/7.0),2) as `Overall Rating`,
vendors.`Phone #`,
vendors.`Fax #`,
vendors.Website,
vendors.`Physical Address`,
vendors.`P.O. Box`,
vendors.City,
vendors.`State`,
vendors.Zip,
vendors.`Region Serving`,
vendors.Note,
vendors.OnVendorList,
vendors.`Search Words`,
ROUND(AVG(`Communication_Rating`),2) AS `Average Communication Rating`,
ROUND(AVG(`Promptness_Rating`),2) AS `Average Promptness Rating`,
ROUND(AVG(`Performance_Rating`),2) AS `Average Performance Rating`,
ROUND(AVG(`Equipment_Rating`),2) AS `Average Equipment Rating`,
ROUND(AVG(`Safety_Rating`),2) AS `Average Safety Rating`,
ROUND(AVG(`Documentation_Rating`),2) AS `Average Documentation Rating`,
ROUND(AVG(`Cost_Rating`),2) AS `Average Cost Rating`
FROM vendors
LEFT JOIN `Vendor Ratings` ON vendors.vendor = `vendor ratings`.vendor

无论我使用哪种类型的联接，我都会得到相同的结果。我的问题出在哪里？

Answer 1

您在供应商上缺少GROUP BY。如果没有 group by 子句，查询只是计算总体聚合值(所有供应商一次组合)。

请尝试以下操作:

SELECT
vendors.ID,
vendors.Vendor,
ROUND(((AVG(`Cost_Rating`) + AVG(`Documentation_Rating`) + AVG(`Safety_Rating`) + AVG(`Equipment_Rating`) + AVG(`Performance_Rating`) + AVG(`Promptness_Rating`) + AVG(`Communication_Rating`))/7.0),2) as `Overall Rating`,
vendors.`Phone #`,
vendors.`Fax #`,
vendors.Website,
vendors.`Physical Address`,
vendors.`P.O. Box`,
vendors.City,
vendors.`State`,
vendors.Zip,
vendors.`Region Serving`,
vendors.Note,
vendors.OnVendorList,
vendors.`Search Words`,
ROUND(AVG(`Communication_Rating`),2) AS `Average Communication Rating`,
ROUND(AVG(`Promptness_Rating`),2) AS `Average Promptness Rating`,
ROUND(AVG(`Performance_Rating`),2) AS `Average Performance Rating`,
ROUND(AVG(`Equipment_Rating`),2) AS `Average Equipment Rating`,
ROUND(AVG(`Safety_Rating`),2) AS `Average Safety Rating`,
ROUND(AVG(`Documentation_Rating`),2) AS `Average Documentation Rating`,
ROUND(AVG(`Cost_Rating`),2) AS `Average Cost Rating`
FROM vendors
LEFT JOIN `Vendor Ratings` ON vendors.vendor = `vendor ratings`.vendor
GROUP BY 
  vendors.ID,
  vendors.Vendor,
  vendors.`Phone #`,
  vendors.`Fax #`,
  vendors.Website,
  vendors.`Physical Address`,
  vendors.`P.O. Box`,
  vendors.City,
  vendors.`State`,
  vendors.Zip,
  vendors.`Region Serving`,
  vendors.Note,
  vendors.OnVendorList,
  vendors.`Search Words` 

另请注意，Select 子句中未使用 Avg() 等聚合函数的所有列均已添加到 Group By条款也是如此。

请阅读:SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by