MySQL8（兼容5.7）关联查询修正真实计数错误

表结构 #

CREATE TABLE topics (
  id INT,
  text VARCHAR(100),
  parent VARCHAR(1)
);

CREATE TABLE sentiment (
  id INT,
  grade INT,
  parent VARCHAR(1)
);

测试数据 #

INSERT INTO topics (id, text, parent) VALUES (1, 'Cryptocurrency', 'A');
INSERT INTO topics (id, text, parent) VALUES (2, 'Cryptocurrency', 'B');
INSERT INTO topics (id, text, parent) VALUES (2, 'ETH', 'B');

INSERT INTO sentiment (id, grade, parent) VALUES (2, 0 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 0 , 'B');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'B');

需求 #

查询每个topics.text的真实出现次数，以及对应相同parent的sentiment.grade总和。

原查询及错误结果 #

原查询语句：

SELECT 
    count(topics.text), 
    topics.text,
    sum(sentiment.grade)
  
   FROM topics
   inner join sentiment on (sentiment.parent = topics.parent)
   group by text

错误结果：

其中Cryptocurrency的真实出现次数应为2，ETH应为1。

修正后的查询 #

兼容MySQL 5.7及以上的简洁版本 #

SELECT 
    COUNT(DISTINCT CONCAT(t.id, t.parent)) AS text_count,
    t.text,
    SUM(s.grade) AS total_grade_sum
FROM topics t
JOIN sentiment s ON t.parent = s.parent
GROUP BY t.text;

无窗口函数的兼容版本（适配MySQL 5.7） #

SELECT 
    t.text_count,
    t.text,
    SUM(s.parent_grade_sum) AS total_grade_sum
FROM (
    SELECT 
        text,
        COUNT(*) AS text_count
    FROM topics
    GROUP BY text
) t
JOIN (
    SELECT 
        tp.text,
        SUM(s_inner.parent_grade_sum) AS parent_grade_sum
    FROM topics tp
    JOIN (
        SELECT parent, SUM(grade) AS parent_grade_sum
        FROM sentiment
        GROUP BY parent
    ) s_inner ON tp.parent = s_inner.parent
    GROUP BY tp.text
) s ON t.text = s.text;

正确结果 #

问题原因 #

原查询直接关联topics和sentiment后分组，导致topics中的每条记录会被sentiment中同parent的所有记录重复关联，从而放大了计数结果。比如Cryptocurrency在parent A有1条记录，关联sentiment的4条记录；在parent B有1条记录，关联sentiment的2条记录，最终计数为14+12=6，而非真实的2次。

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