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

Question

我有一个供应商表和一个供应商评级表。我正在尝试创建一个视图，该视图在左侧显示所有供应商，在右侧显示其总体评级。我希望供应商出现，即使他们还没有被评级。我正在使用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 列表不在 GROUP BY 子句中，并且包含非聚合列....与 sql_mode=only_full_group_by 不兼容