mysql - 简化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