3 个表连接一对多

我在学习连接时遇到了一些麻烦，我正在处理 2 个一对多关系：

在这种情况下，我有很多章节和很多评级的小说

我需要获取小说信息以及与每部小说相关的章节数以及每部小说的平均评分，我正在尝试这样做：

SELECT n.id
     , n.nvl_title
     , COUNT(c.id) AS nvl_chapters
     , AVG(nr.rate_value) as nvl_rating
     , MAX(c.createdAt) AS nvl_last_update
  FROM novels n
  left 
  JOIN novels_ratings nr 
    ON nr.novel_id = n.id
  left 
  JOIN chapters c 
    ON c.nvl_id = n.id 
   AND c.chp_status = 'Active'
 WHERE n.nvl_status IN ("Active", "Finished") 
 GROUP 
    BY n.id;

仅使用章节查询似乎工作得很好，但如果我添加行“left JOIN Novels_ratings nr ON nr.novel_id = n.id”，章节计数会增加到小说的许多评分。

例如：一本有 2 章和 2 个评分的小说总共返回 4 章。

任何帮助都将受到充分重视。

如果有什么我想解释的地方，请告诉我，我会尽力澄清。

我一直在使用一些丑陋的查询来完成这项工作，但是一旦章节表开始有很多寄存器，我就被迫学习更多的光学查询

编辑

我创建了一个小型数据库，足以对查询进行一些测试：

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `chapters` (
  `id` int(11) NOT NULL,
  `nvl_id` int(20) DEFAULT NULL,
  `chp_title` varchar(250) DEFAULT NULL,
  `chp_status` varchar(8) NOT NULL DEFAULT 'Active',
  `createdAt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `chapters` (`id`, `nvl_id`, `chp_title`, `chp_status`, `createdAt`) VALUES
(1, 1, 'generic chapter 1', 'Active', '0000-00-00 00:00:00'),
(2, 1, 'generic chapter 2', 'Active', '0000-00-00 00:00:00');

CREATE TABLE `novels` (
  `id` int(20) NOT NULL,
  `nvl_title` varchar(250) DEFAULT NULL,
  `nvl_status` varchar(8) NOT NULL DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `novels` (`id`, `nvl_title`, `nvl_status`) VALUES
(1, 'generic novel', 'Active');

CREATE TABLE `novels_ratings` (
  `id` int(20) NOT NULL,
  `novel_id` int(20) DEFAULT NULL,
  `rate_value` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `novels_ratings` (`id`, `novel_id`, `rate_value`) VALUES
(1, 1, 3),
(2, 1, 4);

ALTER TABLE `chapters`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `novels`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `novels_ratings`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `chapters`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `novels`
  MODIFY `id` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `novels_ratings`
  MODIFY `id` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

非常感谢你！