mysql - 我已经走进了死胡同。 SQL查询获取最后回复的用户名和时间戳

Question

这是我试图开始工作的查询。

SELECT  
    t.id AS topic_id,
    t.title AS topic_title,
    t.content AS topic_content,
    t.created_at AS topic_created_at,
    t.updated_at AS topic_updated_at,
    t.user_id AS topic_user_id,
    c.id AS comment_id,
    c.content AS comment_content,
    c.created_at AS comment_created_at,
    max_c.username AS comment_username,
    u.username AS topic_username
FROM   
    topics t
JOIN 
    (SELECT 
         c2.topic_id, c2.created_at, u2.username 
     FROM 
         comments c2
     JOIN 
         users u2 ON c2.user_id = u2.id
     JOIN 
         topics t2 ON c2.topic_id = t2.id
     ORDER BY 
         c2.created_at desc) max_c ON t.id = max_c.topic_id
JOIN 
    comments c ON max_c.created_at = c.created_at
JOIN 
    users u ON u.id = t.user_id
ORDER BY 
    c.created_at DESC

非常确定查询的这部分不正确:

SELECT 
    c2.topic_id, c2.created_at, u2.username 
FROM 
    comments c2
JOIN 
    users u2 ON c2.user_id = u2.id
JOIN 
    topics t2 ON c2.topic_id = t2.id
ORDER BY 
    c2.created_at desc

该查询当前显示以下内容。但我想按 created_at 或任何合适的内容进行分组，这样我们只能得到有关主题的最新回复。

如果你能帮忙，那就太棒了，到目前为止我已经花了大约 5 个小时来写这篇文章......

我在下面附上了我的表迁移。

# Dump of table comments
# ------------------------------------------------------------

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `topic_id` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `comments_ibfk_2` (`topic_id`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table topics
# ------------------------------------------------------------

CREATE TABLE `topics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `content` text,
  `user_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table users
# ------------------------------------------------------------

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT 'NOT NULL',
  `email` varchar(255) DEFAULT 'NOT NULL',
  `password` char(60) DEFAULT 'NOT NULL',
  `admin` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

如果我理解你的问题，你遇到的问题是子查询应该只返回最后一个用户名和时间戳，但返回全部。在这种情况下，您可以在 order by 之后使用 LIMIT 1 以获得所需的结果。

    SELECT c2.topic_id, c2.created_at,u2.username 
    FROM comments c2
    JOIN users u2 ON c2.user_id = u2.id
    JOIN topics t2 ON c2.topic_id = t2.id
    ORDER BY c2.created_at desc
    LIMIT 1