mysql - 将计算的 COUNT 列从一个 View 添加到另一 View

Question

我有一个 View 引用包含供应商联系信息的供应商表和一个供应商评级表来显示每个供应商的平均评级。该 View 在提取供应商信息和评级平均值时有效，但我有另一个 View ，它告诉我每个供应商被评级的次数。该 View 工作正常，但我无法让它在我的连接 View 中工作。

这是处理供应商信息和平均评级的 View 的代码:

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 OUTER JOIN `vendor ratings` ON vendors.Vendor = `vendor ratings`.Vendor
GROUP BY vendor

这是我的另一个 View 的代码，显示每个供应商被评级的次数:

SELECT
Vendor,
COUNT(Vendor) AS `COUNT(Vendor)`
FROM `vendor ratings`
GROUP BY Vendor
ORDER BY Vendor

我尝试添加行: COUNT(vendor As COUNT(Vendor), 到已经适用于其他所有内容的代码中，但没有成功。我做错了什么？我只是结果看起来像这样...

供应商、平均评分、评分次数、电话号码等......

Answer 1

在加入之前进行汇总(即使用子查询或“派生表”)

SELECT
      v.*
    , vr.*
FROM
FROM vendors v
LEFT JOIN (
     SELECT
            vendors.Vendor
          , COUNT(*)                                    AS `COUNT(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`
          , 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 `vendor ratings`
     GROUP BY vendors.Vendor
     ) vr ON v.Vendor = vr.Vendor

请注意，MySQL 有一种非常不幸的方式允许 group by子句仅包含 select 子句中存在的少数非聚合列。这不是好的做法，它可能会导致“意外的结果”。 MySQL正在慢慢地改进这方面，但是你应该警惕这个问题here's a start 。研究only_full_group_by

• 请注意，我不建议使用 select v.*, vr.*在您的最终代码中，列出您需要的列，我这样做只是为了将注意力集中在子查询上
• 但我建议您在连接后的所有列前添加相关表前缀。