Mysql多张表关联时的分页查询

示例表信息

# 作者表
CREATE TABLE `test_author` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `author_name` varchar(255) NOT NULL COMMENT '作者名',
  `creator` int(11) NOT NULL COMMENT '创建人',
  `last_updater` int(11) NOT NULL COMMENT '最后更新人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `delete_flag` int(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0-默认，1-删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='作者';

#书籍表
CREATE TABLE `test_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `creator` int(11) NOT NULL COMMENT '创建人',
  `last_updater` int(11) NOT NULL COMMENT '最后更新人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `delete_flag` int(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0-默认，1-删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='书籍';

#关系表
CREATE TABLE `test_topic_paper_related` (
  `book_id` int(11) NOT NULL COMMENT '书籍ID',
  `author_id` int(11) NOT NULL COMMENT '作者ID',
  PRIMARY KEY (`book_id`,`author_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='作者-书籍关联关系';

方法一

SELECT
    t.id,
    t.title,
    t.creator,
    t.last_updater,
    t.create_time,
    t.last_update_time,
    t.delete_flag,
    t2.author_name 
FROM
    (
    SELECT
        t.id,
        t.title,
        t.creator,
        t.last_updater,
        t.create_time,
        t.last_update_time,
        t.delete_flag 
    FROM
        test_book AS t 
    WHERE
        t.id IN (
        SELECT
            book_id 
        FROM
            test_author_book_related 
        WHERE
        author_id IN (筛选项 ID集合 )) 
        AND 其他筛选条件 
    ORDER BY
        t.last_update_time DESC,
        t.id 
        LIMIT 分页信息 
    ) t
    LEFT JOIN test_author_book_related t1 ON t.id = t1.book_id
    LEFT JOIN test_author t2 ON t1.author_id = t2.id