SQL 如何避免在连接两个表时重复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8099747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to avoid duplicate on Joining two tables
提问by zod
Student Table
SID Name
1 A
2 B
3 C
Marks Table
id mark subject
1 50 physics
2 40 biology
1 50 chemistry
3 30 mathematics
SELECT distinct(std.id),std.name,m.mark, row_number() over() as rownum FROM
student std JOIN marks m ON std.id=m.id AND m.mark=50
This result is 2 times A even after using disticnt . My expected result will have only one A. if i remove row_number() over() as rownum its working fine. Why this is happening ? how to resolve. AM using DB2!!
即使在使用 disticnt 之后，这个结果也是 A 的 2 倍。我的预期结果将只有一个 A. 如果我删除 row_number() over() 作为 rownum 其工作正常。为什么会这样？如何解决。正在使用 DB2 ！！
回答by Charles Bretana
There are two rows in marks Table with id = 1 and mark = 50.. So you will get two rows in the output for each row in student table... If you only want one, you have to do a group By
标记表中有两行，id = 1 和标记 = 50.. 所以你会在输出中为学生表中的每一行得到两行......如果你只想要一个，你必须做一个分组
SELECT std.id, std.name, m.mark, row_number()
over() as rownum
FROM student std
JOIN marks m
ON m.id=std.id AND m.mark=50
Group By std.id, std.name, m.mark
回答by sceaj
Now that you've clarified your question as:
现在你已经澄清了你的问题：
I want to find all students with a mark of 50 in at least one subject. I would use the query:
我想找到所有学生至少在一个科目中获得 50 分。我会使用查询：
SELECT student.id, '50'
FROM student
WHERE EXISTS (SELECT 1 FROM marks WHERE marks.id = student.id AND marks.mark = 50)
This also gives you flexibility to change the criteria, e.g. at least one mark of 50 or less.
这也使您可以灵活地更改标准，例如至少一个 50 分或更少的分数。
回答by orbfish
Similar to Charles answer, but you always want to put the predicate (mark=50) in the WHERE clause, so you're filtering before joining. If this is just homework it might not matter but you'll want to remember this if you ever hit any real data.
类似于 Charles 的回答，但您总是希望将谓词 (mark=50) 放在 WHERE 子句中，因此您在加入之前进行了过滤。如果这只是家庭作业，那可能无关紧要，但是如果您遇到任何真实数据，您会想要记住这一点。
SELECT std.sid,
std.name,
m.mark,
row_number() over() AS rownum
FROM student std
JOIN marks m
ON std.sid=m.id
WHERE m.mark=50
GROUP BY std.sid, std.name, m.mark
