修正MySQL查询：获取正确的enrolls与points统计值

我执行了以下MySQL查询语句：

SELECT
    b.id, b.prid, b.title,
    p.fullname,
    m.enroll, m.point,
    SUM(m2.enroll) enrolls, COUNT(m2.point) points,
    COUNT(DISTINCT r.id) reviewal
FROM books b
    JOIN person p ON p.id = b.prid
    LEFT JOIN promotes m ON m.bkid = b.id AND m.prid = p.id
    LEFT JOIN promotes m2 ON m2.bkid = b.id
    LEFT JOIN reviews r ON r.bkid = b.id
GROUP BY b.id 
ORDER BY b.id ASC;

得到的输出为：

但enrolls和points列结果不符合预期，正确结果应为：

表结构及数据 #

CREATE TABLE `books` (
      `id` int(10) UNSIGNED NOT NULL,
      `prid` int(10) UNSIGNED NOT NULL,
      `title` varchar(100) NOT NULL
    );
    
INSERT INTO `books` (`id`, `prid`, `title`) VALUES
(1, 1, 'Physics'), (2, 2, 'Chemistry'), (3, 3, 'Maths');

CREATE TABLE `person` (
  `id` int(10) UNSIGNED NOT NULL,
  `fullname` varchar(100) NOT NULL
);

INSERT INTO `person` (`id`, `fullname`) VALUES
(1, 'Jade'), (2, 'Jack'), (3, 'John');

CREATE TABLE `promotes` (
  `bkid` int(11) UNSIGNED NOT NULL,
  `prid` int(11) UNSIGNED NOT NULL,
  `enroll` tinyint(1) DEFAULT 1,
  `point` tinyint(1) DEFAULT NULL
);

INSERT INTO `promotes` (`bkid`, `prid`, `enroll`, `point`) VALUES
(1, 1, 1, 2), (2, 1, 1, 3), (2, 2, 1, 1),
(3, 1, NULL, 0), (3, 2, 0, NULL), (3, 3, 1, NULL);

CREATE TABLE `reviews` (
  `id` int(10) NOT NULL,
  `prid` int(10) UNSIGNED NOT NULL,
  `bkid` int(10) UNSIGNED NOT NULL,
  `review` varchar(500) NOT NULL
);

INSERT INTO `reviews` (`id`, `prid`, `bkid`, `review`) VALUES
(1, 1, 2, 'Nice'), (2, 1, 3, 'Thinking'), (3, 2, 2, 'Try it');

ALTER TABLE `books` ADD PRIMARY KEY (`id`),
ADD KEY `book_prid_fk` (`prid`);

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

ALTER TABLE `promotes` ADD PRIMARY KEY (`bkid`,`prid`),
ADD KEY `promotes_prid_fk` (`prid`);

ALTER TABLE `reviews` ADD PRIMARY KEY (`id`),
ADD KEY `reviews_prid_fk` (`prid`), ADD KEY `reviews_bkid_fk` (`bkid`);

ALTER TABLE `books`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
  
ALTER TABLE `person`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;  

ALTER TABLE `reviews`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
  
ALTER TABLE `books`
  ADD CONSTRAINT `book_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);
  
ALTER TABLE `promotes`
  ADD CONSTRAINT `promotes_bkid_fk` FOREIGN KEY (`bkid`) REFERENCES `books` (`id`),
  ADD CONSTRAINT `promotes_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);
  
ALTER TABLE `reviews`
  ADD CONSTRAINT `reviews_bkid_fk` FOREIGN KEY (`bkid`) REFERENCES `books` (`id`),
  ADD CONSTRAINT `reviews_prid_fk` FOREIGN KEY (`prid`) REFERENCES `person` (`id`);

promotes表中，enroll列取值可为null、0或1，point列取值可为null或1-5。

问题原因 #

原查询同时关联promotes表两次，还关联了reviews表，会产生笛卡尔积：比如book 2有2条promotes记录和2条reviews记录，关联后生成4条重复数据，导致SUM和COUNT基于重复数据计算，结果偏大。此外，原查询直接SUMenroll会把0计入统计，不符合实际需求（仅需统计enroll=1的记录）。

正确查询语句 #

先对promotes和reviews表分别做聚合计算，再关联主查询，避免笛卡尔积：

SELECT
    b.id, b.prid, b.title,
    p.fullname,
    m.enroll, m.point,
    p_stats.enrolls,
    p_stats.points,
    COALESCE(r_stats.reviewal, 0) AS reviewal
FROM books b
JOIN person p ON p.id = b.prid
LEFT JOIN promotes m ON m.bkid = b.id AND m.prid = p.id
LEFT JOIN (
    SELECT 
        bkid,
        SUM(CASE WHEN enroll = 1 THEN 1 ELSE 0 END) AS enrolls,
        COUNT(point) AS points
    FROM promotes
    GROUP BY bkid
) p_stats ON p_stats.bkid = b.id
LEFT JOIN (
    SELECT 
        bkid,
        COUNT(id) AS reviewal
    FROM reviews
    GROUP BY bkid
) r_stats ON r_stats.bkid = b.id
ORDER BY b.id ASC;

验证结果 #

执行上述查询后，结果符合预期：

内容的提问来源于stack exchange，提问作者Rajan Sharma