Mysql8加入并统计独特的真实外观

Question

我有以下天赋：

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的计数和sentiment.grade的共享父和
所以我提出了以下查询：

SELECT 
count(topics.text), 
topics.text,
sum(sentiment.grade)

FROM topics
inner join sentiment on (sentiment.parent = topics.parent)
group by text

结果：

| count(topics.text) | sum(sentiment.grade) | text           |
| ------------------ | -------------------- | -------------- |
| 6                  | 4                    | Cryptocurrency |
| 2                  | 1                    | ETH            |
---

我只对第一列有问题，Cryptocurrency的实数是2，ETH的实数是1。

你能解决这个问题吗
(我使用的是mysql8，如果可能的话，我很高兴有5.7兼容(

DB Fiddle 视图

Answer 1

SELECT 
count(distinct t.id), 
t.text,
sum(s.grade)      
FROM topics t
JOIN sentiment s on s.parent = t.parent
GROUP BY t.text

Answer 2

由于topics中有两行text=cryptocurrency，一行parent=A，另一行parent=B，当您加入时，您应该会看到crpytocurrency的6行(topics的第一行与sentiment的前四行匹配，topics的第二行与sentiment的最后两行匹配(。您可以看到，如果您将原始查询更改为以下查询：

SELECT 
*
FROM topics
inner join sentiment on (sentiment.parent = topics.parent)

我想你想看看具有相同text的topics的数量，以及他们父母的总grades(对于cryptocurrency，是A和B的总和(。这可以帮助你：

SELECT 
topics_count.n_text,
topics.text,
SUM(sentiment.grade)

FROM topics
INNER JOIN (SELECT text, count(*) 'n_text' FROM topics GROUP BY text) topics_count ON topics.text = topics_count.text
INNER JOIN sentiment ON (sentiment.parent = topics.parent)
GROUP BY text