MySQL UPDATE，MAX，JOIN查询

一尘不染

MySQL UPDATE，MAX，JOIN查询

sql

我有两张桌子：-

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;

有麻烦…非常感谢所有帮助。

阅读 251

2021-05-16

共1个答案

一尘不染

据我所知，我认为这可以解决问题，我MAX(Product_ID)只是用来解决所有重复项，其中相同制造商的2个产品可能具有相同的得分，并且两者均为最高得分。您可能希望以其他方式解决重复项。

UPDATE  summary_table
SET     max_score = 
        (   SELECT  MAX(m.Product_ID) [MaxScoreProductID] 
            FROM    manu_table m
                    INNER JOIN product_table p
                        ON m.Product_ID = p.Product_ID
                    INNER JOIN
                    (   SELECT  Manufacturer, MAX(Score) [MaxScore]
                        FROM    manu_table m
                                LEFT JOIN product_table p
                                    ON m.Product_ID = p.Product_ID
                        GROUP BY Manufacturer
                    ) ms
                        ON ms.Manufacturer = m.Manufacturer
                        AND ms.MaxScore = p.Score
            WHERE   m.Manufacturer = summary_table.Manufacturer
            GROUP BY m.Manufacturer
        )

2021-05-16