MySQL UPDATE、MAX、JOIN 查询

Question

我有两个表:-

manu_table
product_id, manufacturer
1, ford
2, ford
3, toyota

product_table
product_id, score
1, 80
2, 60
3, 40

我想将每个制造商得分最高的 product_id 存储在汇总表中:-

summary_table
manufacturer, max_score
ford,   1
toyota, 3

到目前为止我有:-

UPDATE summary_table st
SET max_score = (
                 SELECT product_id 
                 FROM (
                       SELECT manufacturer, product_id, max(score) as ms 
                       FROM manu_table 
                       LEFT JOIN product_table USING (product_id) 
                       group by product_id) t)
WHERE st.manufacturer = manu_table.manufacturer;

遇到麻烦...非常感谢所有帮助。

Answer 1

试试这个查询-

UPDATE summary_table st
  JOIN (
    SELECT mt.manufacturer, MAX(pt.score) max_score FROM manu_table mt
    JOIN product_table pt
      ON mt.product_id = pt.product_id
    GROUP BY mt.manufacturer) t
  ON t.manufacturer = st.manufacturer
SET st.max_score = t.max_score

---

此查询将 product_id 设置为最高分:

UPDATE summary_table st
  JOIN (SELECT t1.* FROM 
  (SELECT mt.*, pt.score FROM manu_table mt JOIN product_table pt ON mt.product_id = pt.product_id) t1
  JOIN (
    SELECT mt.manufacturer, MAX(pt.score) max_score FROM manu_table mt
     JOIN product_table pt
       ON mt.product_id = pt.product_id
     GROUP BY mt.manufacturer
  ) t2
  ON t1.manufacturer = t2.manufacturer AND t1.score = t2.max_score
  ) t
  ON t.manufacturer = st.manufacturer
SET st.max_score = t.product_id