mysql - 将 MySQL 表更新为构造的双聚合，这取决于表本身

Question

我需要更新一个表，但为了获取新值，我似乎需要创建一个临时表。原因是我需要计算 max 的 sum。我能做到吗？

伪代码如下所示:

 UPDATE users u SET usersContribution = [CREATE TEMPORARY TABLE IF NOT EXISTS tmpTbl3 AS
          (SELECT ROUND(max(zz.zachetTimestamp - d.answerDate)) as answerDateDiff
           FROM zachet zz
           JOIN discussionansw d ON d.zachetid=zz.zachetId and d.usersid=zz.usersId and
                                    zz.zachetTimestamp > d.answerDate
           WHERE zz.whoTalk=u.userid and
                 NOT EXISTS (SELECT * FROM discussionansw
                            WHERE zachetid=zz.zachetId and usersid=u.userid')
           GROUP BY zz.zachetId)]
           SELECT SUM(answerDateDiff) FROM tmpTbl3;"

我使用括号来显示必须完成的部分，但被 UPDATE 查询忽略...

我有 max 和 sum，但我没有找到避免 tmp 表的方法。但如果可以的话，我们会很高兴能有这样的解决方案。

<小时/>

我把答案放在这里，这是我在@flaschenpost和这篇文章的帮助下得到的:SQL Update to the SUM of its joined values

 CREATE TEMPORARY TABLE IF NOT EXISTS t0tmpTbl3 AS
          (SELECT zz.whoTalk, ROUND(max(zz.zachetTimestamp - d.answerDate)) as answerDateDiff
           FROM zachet zz
           JOIN discussionansw d ON d.zachetid=zz.zachetId and d.usersid=zz.usersId and
                                    zz.zachetTimestamp > d.answerDate
           WHERE 
                 NOT EXISTS (SELECT * FROM discussionansw WHERE zachetid=zz.zachetId and usersid=zz.whoTalk)
           GROUP BY zz.zachetId);

 UPDATE users u
           JOIN (SELECT whoTalk, SUM(answerDateDiff) sumAnswerDateDiff
                 FROM t0tmpTbl3 GROUP BY whoTalk) t
           ON u.usersId=t.whoTalk
           SET u.usersContribution=sumAnswerDateDiff;

Answer 1

你能把它分成两个查询吗？

drop temporary table if exists tmp_maxsumofsomething;

create temporary table tmp_maxsumofsomething 
select max(), sum(), ...
from zachet z inner join discussionansw a on ...
group by...
;

update u inner join tmp_maxsumofsomething t on ... set u.... = t...

临时表仅在创建它们的连接中可见，因此具有线程安全性。

编辑:只要您的查询有意义，您就可以尝试:

 DROP TEMPORARY TABLE IF EXISTS tmpTbl3;
 CREATE TEMPORARY TABLE tmpTbl3
      SELECT zz.whoTalk as userId, ROUND(max(zz.zachetTimestamp - d.answerDate)) as answerDateDiff
       FROM zachet zz, discussionansw d
      WHERE  d.zachetid=zz.zachetId 
        and d.usersid=zz.usersId and zz.zachetTimestamp > d.answerDate
            # What do you mean ? by: 
            # and (SELECT count(*) FROM discussionansw
            #      WHERE zachetid=zz.zachetId and usersid=u.userid) = 0
            # Think about a reasonable WHERE NOT EXISTS clause!
     GROUP BY zz.whoTalk

然后你就有了要加入的临时表:

 update users u 
 inner join tmpTbl3 tm on u.userId = tm.userId 
 set u.usersContribution = tm.answerDateDiff

如果您有足够的勇气编写需要这些查询的应用程序，那么您不应该害怕学习更多 SQL 和 MySQL 的一些概念。您来这里是为了探索概念，而不是免费雇用程序员。