时间戳

首页 > 解决方案 > 多个左连接显示与最后日期不同的结果

mysql - 多个左连接显示与最后日期不同的结果

问题描述

我想建立一个讨论板。因此我有 3 个表：t_posts、t_boards、t_board_categories。帖子包含线程（其中 post_post_id 为 NULL）和回复线程（其中 post_post_id 不为 NULL），因此它是自引用的。现在我想在所有类别的所有版块中显示最后 5 个帖子（主题和/或回复）。当前的 sql 如下所示：

SELECT p.post_id, 
       p.post_board_id, 
       p.post_user_id, 
       p2.post_title, 
       bc.bc_name, 
       Max(p.post_date) post_date 
FROM   t_posts p 
       LEFT JOIN t_posts p2 
              ON ( p2.post_post_id IS NULL 
                   AND ( p2.post_id = p.post_id 
                          OR p.post_post_id = p2.post_id ) ) 
       LEFT JOIN t_boards b 
              ON b.board_id = p.post_board_id 
       LEFT JOIN t_board_categories bc 
              ON bc.bc_id = b.board_bc_id 
WHERE  p.post_system_id = '1' 
       AND p.post_deleted = 0 
       AND bc.bc_deleted = 0 
       AND b.board_deleted = 0 
GROUP  BY p.post_id, 
          p2.post_id 
ORDER  BY p2.post_date DESC 
LIMIT  5

结果几乎是我想要的，除了一个线程中的多个回复将导致只显示一个线程。我想要的是 5 个不同的线程与最新的答案一起显示，而不仅仅是一个线程 5 次。

我加入 t_posts p2 以显示原始线程标题。

我还尝试将 DISTINCT p2.post_id 添加到 SELECT 中，但它不会改变任何东西。

架构：

CREATE TABLE `t_board_categories` (
 `bc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `bc_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT '',
 `bc_prio` int(11) NOT NULL DEFAULT '0',
 `bc_system_id` int(10) unsigned NOT NULL DEFAULT '0',
 `bc_deleted` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`bc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `t_boards` (
 `board_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `board_bc_id` int(10) unsigned DEFAULT NULL,
 `board_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
 `board_subtitle` text COLLATE utf8_unicode_ci,
 `board_prio` int(11) NOT NULL DEFAULT '0',
 `board_system_id` int(10) unsigned NOT NULL DEFAULT '0',
 `board_deleted` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`board_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `t_posts` (
 `post_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_user_id` bigint(20) unsigned DEFAULT NULL,
 `post_title` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
 `post_text` text COLLATE utf8_unicode_ci NOT NULL,
 `post_post_id` bigint(20) unsigned DEFAULT NULL,
 `post_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `post_board_id` int(10) unsigned NOT NULL DEFAULT '0',
 `post_system_id` int(10) unsigned NOT NULL DEFAULT '0',
 `post_deleted` int(2) NOT NULL DEFAULT '0',
 PRIMARY KEY (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=205 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO `t_board_categories` (`bc_id`, `bc_name`, `bc_prio`, `bc_system_id`, `bc_deleted`) VALUES (1, 'Test-Category', '1', '1', '0');

INSERT INTO `t_boards` (`board_id`, `board_bc_id`, `board_name`, `board_subtitle`, `board_prio`, `board_system_id`, `board_deleted`) VALUES (1, '1', 'Test-Board', 'Board Subtitle', '1', '1', '0');

INSERT INTO `t_posts` (`post_id`, `post_user_id`, `post_title`, `post_text`, `post_post_id`, `post_date`, `post_board_id`, `post_system_id`, `post_deleted`) VALUES
(1, 14, 'Thread 1', 'Test1', NULL, '2019-05-22 00:18:25', 1, 1, 0),
(2, 14, 'Thread 2', 'Test2', NULL, '2019-05-22 00:18:44', 1, 1, 0),
(3, 14, 'Thread 3', 'Test 3', NULL, '2019-05-22 00:18:51', 1, 1, 0),
(4, 19, 'Thread 4', 'Test 4', NULL, '2019-05-22 00:19:02', 1, 1, 0),
(5, 19, 'Thread 5', 'Test 5', NULL, '2019-05-22 00:19:07', 1, 1, 0),
(6, 19, 'Thread 6', 'Test 6', NULL, '2019-05-22 00:19:15', 1, 1, 0),
(7, 14, 'Reply', 'A', 5, '2019-05-22 00:19:46', 1, 1, 0),
(8, 14, 'Reply', 'B', 5, '2019-05-22 00:19:47', 1, 1, 0),
(9, 14, 'Reply', 'C', 5, '2019-05-22 00:19:49', 1, 1, 0);

预期结果：线程 1-5 的一行，带有用户 ID 和最后回复日期（线程 5 的用户 ID：14，日期：2019-05-22 00:19:49，线程 1-4 的最后日期是线程-日期，用户ID是线程作者用户ID）

我整个晚上都在做这个，很高兴能得到一些帮助。先感谢您！

标签： mysqlsql

解决方案

您的示例数据表明，帖子级别不能超过两个级别（post_post_id不能post_post_id是不为空的帖子的 ID）。假设，您可以尝试使用相关的子查询来获取子帖子和帖子的帖子，按日期降序排列它们并使用LIMIT来选择最上面的。还按最后一个子帖子的日期降序对主查询进行排序，并用于LIMIT获得前 5 个。类似于以下内容：

SELECT bc1.bc_id,
       bc1.bc_name,
       b1.board_id,
       b1.board_name,
       p1.post_id,
       p1.post_title,
       (SELECT p2.post_title
               FROM t_posts p2
               WHERE p1.post_id IN (p2.post_id,
                                    p2.post_post_id) 
               ORDER BY p2.post_date DESC
               LIMIT 1) last_post_title,
       (SELECT p2.post_user_id
               FROM t_posts p2
               WHERE p1.post_id IN (p2.post_id,
                                    p2.post_post_id)
               ORDER BY p2.post_date DESC
               LIMIT 1) last_post_user_id,
       (SELECT p2.post_date
               FROM t_posts p2
               WHERE p1.post_id IN (p2.post_id,
                                    p2.post_post_id) 
               ORDER BY p2.post_date DESC
               LIMIT 1) last_post_date
       FROM t_posts p1
            INNER JOIN t_boards b1
                       ON b1.board_id = p1.post_board_id
            INNER JOIN t_board_categories bc1
                       ON bc1.bc_id = b1.board_bc_id
       WHERE p1.post_post_id IS NULL
       ORDER BY last_post_date DESC
       LIMIT 5;

db<>小提琴

推荐阅读