在MySQL的另一个表中插入列中的数据

发布于 2025-01-29 02:04:55 字数 520 浏览 4 评论 0原文

我有一个名为反馈的表，其中包含列Mechemail（forefer键）和rate。

我有另一个名为机械的表<代码>电子邮件（主键）和avgrate。

我想将平均速率从反馈表插入机械表中Mechemail匹配email> email。

这是我使用的代码，但仅选择 - 但不插入：

SELECT 
    mechanical.email, 
    COALESCE(AVG(feedback.rate), 0) AS rate
FROM 
    mechanical
LEFT JOIN 
    feedback ON feedback.mechEmail = mechanical.email
GROUP BY 
    mechanical.email;

原文

I have a table named feedback which has columns mechEmail (foreign key) and rate in it.

I have another table named mechanical which has columns email (primary key) and avgrate.

I want to insert the average rate from the feedback table into the mechanical table where mechEmail matches email.

Here's the code that I used, but it only selects - but does not insert:

SELECT 
    mechanical.email, 
    COALESCE(AVG(feedback.rate), 0) AS rate
FROM 
    mechanical
LEFT JOIN 
    feedback ON feedback.mechEmail = mechanical.email
GROUP BY 
    mechanical.email;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甩你一脸翔 2025-02-05 02:04:55

看来您不需要插入语句，而是一个更新语句，因为您已经在机械表中有电子邮件 -

UPDATE mechanical M
  JOIN (SELECT mechEmail, AVG(rate) rate
          FROM feedback
         GROUP BY mechEmail) F ON F.mechEmail = M.email
   SET M.avgrate = F.rate;

It seems you don't want an Insert statement but an UPDATE statement as you already have email in mechanical table -

UPDATE mechanical M
  JOIN (SELECT mechEmail, AVG(rate) rate
          FROM feedback
         GROUP BY mechEmail) F ON F.mechEmail = M.email
   SET M.avgrate = F.rate;

回复收藏 0 原文

~没有更多了~