单行多次重复

Question

我有两张这样的桌子：

summary(id, status, value, time, aperson) and long_summary(id, who, comment, alltext)

为此，我尝试了以下查询：

SELECT l.id,summary.status
FROM  long_summary l INNER JOIN summary ON l.id = summary.id INNER JOIN long_summary ON summary.aperson = long_summary.who 
WHERE summary.status IN('old','new','waiting')
AND summary.value IN ('') 
AND summary.time >= DATE_SUB(NOW(),INTERVAL 2 MONTH)
AND l.alltext LIKE '% relational database management system %' ORDER BY FIELD(summary.status,'old','new','waiting'),summary.time DESC

在运行时，它会给出正确的输出，但返回的行会混合多次，如：

(1,'old')
(1,'old')
(1,'old')
(2,'new')
(2,'new')
(3,'new')
(4,'waiting')
(4,'waiting')

为此i have used SELECT DISTINCT and after that it is giving correct output without repetition of the single row。但我不明白我做错了什么？ 请你建议一下好吗？你知道吗

Answer 1

查看发生了什么的方法是（a）创建一个精简的测试用例，以及（b）执行SELECT *，这样您就可以在其他列中看到您得到了什么，这导致了重复。你知道吗

所以，我这样做了：

CREATE TABLE summary(id, status, value, time, aperson);
INSERT INTO "summary" VALUES(1,'old',23,'time0','joe');
INSERT INTO "summary" VALUES(2,'new',42,'time1','bob');
INSERT INTO "summary" VALUES(3,'new',32,'time2','mike');
CREATE TABLE long_summary(id, who, comment, alltext);
INSERT INTO "long_summary" VALUES(1,'someone','i say!','some text');
INSERT INTO "long_summary" VALUES(1,'joe','joe likes','some text');
INSERT INTO "long_summary" VALUES(2,'joe','joe likes bob','some text');
INSERT INTO "long_summary" VALUES(3,'joe','joe likes mike','some text');
INSERT INTO "long_summary" VALUES(1,'bob','nice one, joe','some text');
INSERT INTO "long_summary" VALUES(2,'bob','nice one, me','some text');
INSERT INTO "long_summary" VALUES(2,'bob','double nice one, me','some text');
INSERT INTO "long_summary" VALUES(3,'bob','nice one, mike','some text');
COMMIT;

然后我对你的问题做了一个简单的解释：

SELECT l.id,summary.status
FROM  long_summary l INNER JOIN summary ON l.id = summary.id INNER JOIN long_summary ON summary.aperson = long_summary.who 
WHERE summary.status IN('old','new','waiting')

其他东西都不能让事情变得更糟，对吧？所以这无关紧要。当我运行这个的时候会得到什么？9份1|old，12份2|new。你知道吗

所以，我们把它改成整行：

SELECT *
FROM  long_summary l INNER JOIN summary ON l.id = summary.id INNER JOIN long_summary ON summary.aperson = long_summary.who 
WHERE summary.status IN('old','new','waiting')

1|bob|nice one, joe|some text|1|old|23|time0|joe|1|joe|joe likes|some text
1|bob|nice one, joe|some text|1|old|23|time0|joe|2|joe|joe likes bob|some text
1|bob|nice one, joe|some text|1|old|23|time0|joe|3|joe|joe likes mike|some text
1|joe|joe likes|some text|1|old|23|time0|joe|1|joe|joe likes|some text
1|joe|joe likes|some text|1|old|23|time0|joe|2|joe|joe likes bob|some text
1|joe|joe likes|some text|1|old|23|time0|joe|3|joe|joe likes mike|some text
1|someone|i say!|some text|1|old|23|time0|joe|1|joe|joe likes|some text
1|someone|i say!|some text|1|old|23|time0|joe|2|joe|joe likes bob|some text
1|someone|i say!|some text|1|old|23|time0|joe|3|joe|joe likes mike|some text
2|bob|double nice one, me|some text|2|new|42|time1|bob|1|bob|nice one, joe|some text
2|bob|double nice one, me|some text|2|new|42|time1|bob|2|bob|double nice one, me|some text
2|bob|double nice one, me|some text|2|new|42|time1|bob|2|bob|nice one, me|some text
2|bob|double nice one, me|some text|2|new|42|time1|bob|3|bob|nice one, mike|some text
2|bob|nice one, me|some text|2|new|42|time1|bob|1|bob|nice one, joe|some text
2|bob|nice one, me|some text|2|new|42|time1|bob|2|bob|double nice one, me|some text
2|bob|nice one, me|some text|2|new|42|time1|bob|2|bob|nice one, me|some text
2|bob|nice one, me|some text|2|new|42|time1|bob|3|bob|nice one, mike|some text
2|joe|joe likes bob|some text|2|new|42|time1|bob|1|bob|nice one, joe|some text
2|joe|joe likes bob|some text|2|new|42|time1|bob|2|bob|double nice one, me|some text
2|joe|joe likes bob|some text|2|new|42|time1|bob|2|bob|nice one, me|some text
2|joe|joe likes bob|some text|2|new|42|time1|bob|3|bob|nice one, mike|some text

好了，现在你可以看到问题了，让我们看看为什么会这样。每一行都应该在那里吗？换句话说，如果前三个组合中至少有一个存在，您是否应该看到1|old？你知道吗

如果不是，哪些不应该引起它？您需要在WHERE或JOIN中过滤出一些内容。你知道吗

如果是这样，那么您需要一个GROUP BY来合并相关字段，或者在某个地方需要一个OR，更可能是第一个。你知道吗

一步一步地检查所有问同一问题的小组。如果在实际显示的列上需要GROUP BY，那么使用SELECT DISTINCT就更简单了。你知道吗

您可能还想后退一步，询问您是否真的想要一个完整的JOINlong\u summary with summary with long\u summary。那就是8*3*8=192行，你已经过滤到21行了。这有意义吗，或者你只期望，比如说，过滤掉24行？如果是后者，你就错了。要么这些JOIN中的一个根本不应该在那里，要么它应该是一对一而不是一对多JOIN，要么它出了问题。你知道吗

顺便说一下，您可能可以从我上面的测试中看出我使用了sqlite3而不是mysql，只是因为它的获取和运行要简单得多。我怀疑这有什么不同，但是如果你在mysql中测试并看到不同的结果，请务必告诉我。你知道吗