简化UPDATE语句中的多个子查询

Question

我想让我的Flarum论坛在手动干预数据库后更新其讨论记录。(Flarum仍处于测试阶段，仍然缺乏许多功能，因此手动修复并不少见。)我设法编写了以下查询，它完成了我想要的操作：

UPDATE discussions as d SET
  d.start_time =
    (SELECT min(p.time) FROM posts as p
         WHERE p.discussion_id = d.id),
  d.last_time =
    (SELECT max(p.time) FROM posts as p
         WHERE p.discussion_id = d.id),
  d.comments_count =
    (SELECT count(*) FROM posts as p
         WHERE p.discussion_id = d.id AND p.type = 'comment'),
  d.participants_count =
    (SELECT count(DISTINCT p.user_id) FROM posts as p
         WHERE p.discussion_id = d.id),
  d.start_post_id =
    (SELECT p.id FROM posts as p
         WHERE p.discussion_id = d.id
         ORDER BY p.number ASC LIMIT 1),
  d.start_user_id =
    (SELECT p.user_id FROM posts as p
         WHERE p.discussion_id = d.id
         ORDER BY p.number ASC LIMIT 1),
  d.last_post_id =
    (SELECT p.id FROM posts as p
         WHERE p.discussion_id = d.id
         ORDER BY p.number DESC LIMIT 1),
  d.last_post_number =
    (SELECT p.number FROM posts as p
         WHERE p.discussion_id = d.id
         ORDER BY p.number DESC LIMIT 1),
  d.last_user_id =
    (SELECT p.user_id FROM posts as p
         WHERE p.discussion_id = d.id
         ORDER BY p.number DESC LIMIT 1);

但它看起来很丑陋。我确信有一种方法可以用更简洁、更有效的方式编写相同的逻辑，但我并不是很喜欢SQL，不知道如何做到这一点。有人能告诉我如何消除这些几乎重复的子查询吗？

上面提到的表格定义如下(省略了一些细节)：

CREATE TABLE discussions (
  id                   int unsigned NOT NULL AUTO_INCREMENT,
  title                varchar(200) NOT NULL,
  comments_count       int(10) unsigned NOT NULL DEFAULT '0',
  participants_count   int(10) unsigned NOT NULL DEFAULT '0',
  number_index         int(10) unsigned NOT NULL DEFAULT '0',
  start_time           datetime NOT NULL,
  start_user_id        int(10) unsigned DEFAULT NULL,
  start_post_id        int(10) unsigned DEFAULT NULL,
  last_time            datetime DEFAULT NULL,
  last_user_id         int(10) unsigned DEFAULT NULL,
  last_post_id         int(10) unsigned DEFAULT NULL,
  last_post_number     int(10) unsigned DEFAULT NULL,
  ... );

CREATE TABLE posts (
  id                   int(10) unsigned NOT NULL AUTO_INCREMENT,
  discussion_id        int(10) unsigned NOT NULL,
  number               int(10) unsigned DEFAULT NULL,
  time                 datetime NOT NULL,
  user_id              int(10) unsigned DEFAULT NULL,
  type                 varchar(100) DEFAULT NULL,
  ... );

Flarum使用MySQL作为其主存储后端，因此特定于MySQL的解决方案就可以了。但是，如果有人知道如何在ANSI-SQL中解决这个问题，那就太好了。

Answer 1

您可以使用内连接，并使用max和min作为first和last

UPDATE discussions as d 
INNER JOIN posts as p on d.id = p.discussion_id
SET d.start_time = min(p.time),
    d.last_time = max(p.time),
    d.comments_count = count(*),
    d.participants_count = count(DISTINCT p.user_id) ,
    d.start_post_id = min(p.id ),
    d.start_user_id = min(p.user_id  ),
    d.last_post_id =  max(  p.id ),
    d.last_post_number = max( p.number ),
    d.last_user_id = max( p.user_id  ),
    d.comments_count = sum( case when p.type = 'comment' then 1 else 0)
GROUP BY d.id