我有两个表,一个是供应商表,另一个是供应商反馈表 - 我该如何计算每个供应商的平均评分?目前我有这个:
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表将有许多记录,但第一条记录必须基于各种原因而被忽略。