从另一个MySQL表中计算平均值

Question

我有两个表，一个是供应商表，另一个是供应商反馈表 - 我该如何计算每个供应商的平均评分？目前我有这个：

SELECT s.premium, s.supplier_id, s.name, s.phone, s.website, 
    s.price_low, s.price_high, s.address1, s.address2, s.town,
    s.county, s.postcode, 
    (SUM( f.rating ) / ( COUNT( f.rating ) -1 )) AS rate, 
    GROUP_CONCAT( REPLACE( t.name, ' ', ',' ) SEPARATOR ',' ) AS tags
FROM suppliers AS s
JOIN suppliers_to_tags AS st ON st.supplier_id = s.supplier_id
JOIN supplier_tags AS t ON t.tag_id = st.tag_id
JOIN supplier_feedback AS f ON s.supplier_id = f.supplier_id
GROUP BY s.supplier_id
HAVING tags LIKE '%HI%'
ORDER BY s.premium DESC
LIMIT 0 , 30

但我得到的结果非常奇怪，肯定是不正确的。

supplier_feedback表将有许多记录，但第一条记录必须基于各种原因而被忽略。

- Mike Griffiths

---

你正在使用 AVG 函数吗？http://www.w3schools.com/SQL/sql_func_avg.asp - Adrian

你是想忽略每个供应商的supplier_feedback表中的第一条记录，还是只想获取该表中的第一条记录？ - Chris

Answer 1

使用子查询可以使事情更简单，这样您就可以单独测试子查询的正确性，而不受连接其他表的影响。

(SELECT supplier_id, AVG(rating) AS avg_rating 
FROM supplier_feedback WHERE feedback_id != x GROUP BY supplier_id)

假设 feedback_id（反馈表的主键）不变，您可以从子查询中排除要忽略的记录，其余记录将被平均。

然后，您可以使用以下方法进行连接：

    SELECT s.*, r.avg_rating
    FROM suppliers s
    JOIN 
    (SELECT supplier_id, AVG(rating) AS avg_rating 
    FROM supplier_feedback WHERE feedback_id != x GROUP BY supplier_id) r
    ON s.supplier_id = r.supplier_id

- Chris

Answer 2

标签连接导致反馈行出现多次。这会导致平均值偏离正常。您可以使用子查询重写FROM部分，包括标签和平均值。这可以确保它们的计算不会相互干扰：

SELECT
     <other columns>
,    feedback.rating
,    suptags.tags
FROM suppliers AS s
JOIN (
    SELECT 
        st.supplier_id
    ,   GROUP_CONCAT(REPLACE( t.name, ' ', ',') SEPARATOR ',') AS tags
    FROM suppliers_to_tags AS st 
    JOIN supplier_tags AS t ON t.tag_id = st.tag_id
    GROUP BY st.supplier_id
) as suptags ON suptags.supplier_id = s.supplier_id
JOIN (
    SELECT 
        fb1.supplier_id
    ,   AVG(fb1.rating) as rating
    FROM supplier_feedback fb1
    WHERE fb1.feedback_id NOT IN (
        SELECT min(fb2.feedback_id) 
        FROM supplier_feedback fb2
        WHERE fb2.supplier_id = fb1.supplier_id
    )
    GROUP BY fb1.supplier_id
) feedback ON s.supplier_id = feedback.supplier_id

suptags子查询将每个供应商的所有标签添加到一个字符串中。 feedback子查询计算平均评分，不包括最低反馈ID的行。

- Andomar