SQL 内部连接返回重复记录访问sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22849238/
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
Inner join returning duplicate records access sql
提问by vuyy1182
I have the below table. the only difference in data is suff, wt; the rest of them look the same.
我有下表。数据的唯一区别是suff, wt；其余的看起来都一样。
Things table
东西表
Prefix Baseletter suff CSCcode Description WT BNO BNO-CSCcode
EIGC A5560 BGA 04020 blah1 0 5560 5560-04020
EIGC A5560 HEA 04020 blah2 17.9 5560 5560-04020
Mapp table
映射表
BNO BNO-CSCcode EID Description
5560 5560-04020 4005 blah1
5560 5560-04020 4011 blah2
I'm trying to inner join them using BNO-CSCcodeto get EID for corresponding BNO. But my query is returning duplicates. I'm getting 4 records, even though the first table only has two records.
我正在尝试使用BNO-CSCcode内部加入它们以获取相应BNO 的EID 。但我的查询返回重复项。我得到 4 条记录，即使第一个表只有两条记录。
My SQL query:
我的 SQL 查询：
SELECT
Things.Prefix ,
Things.Baseletter,
Things.suff,
Things.CSCcode,
Things.WT,
Mapping.BNO-CSCcode,
Mapping.EID
FROM
Things
INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode
Why am I getting these duplicates, and how can I fix that?
为什么我会收到这些重复项，我该如何解决？
回答by paqogomez
BNO-CSCcodecontains duplicates. You are joining the first record of Thingsto both records of Mapp, then the second record of Thingsjoins to both records of Mapp. Giving you a total of 4 records.
BNO-CSCcode包含重复项。您将 的第一Things条Mapp记录Things连接到 的两条记录，然后将第二条记录连接到 的两条记录Mapp。一共给你4条记录。
If you want to join these together, you need some unique way of identifying the rows between the tables.
如果您想将这些连接在一起，您需要一些独特的方法来识别表之间的行。
A Distinct should bring it back down to 2 records:
Distinct 应将其恢复为 2 条记录：
SELECT DISTINCT
Things.Prefix,
Things.Baseletter,
Things.suff,
Things.CSCcode,
Things.WT,
Mapping.BNO-CSCcode,
Mapping.EID
FROM
Things
INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode
回答by Monty Wild
You are getting duplicates because both records in the Thingstable have a BNO-CSCcodeof 5560-04020, as do both records in the Mapptable. The only distinction between the two that I can see is the Descriptionfield. So, if you use the following query:
您得到重复项，因为Things表中的两条记录的aBNO-CSCcode均为 5560-04020，表中的两条记录也是如此Mapp。我能看到的两者之间的唯一区别是Description领域。因此，如果您使用以下查询：
SELECT Things.Prefix ,Things.Baseletter,Things.suff,Things.CSCcode,Things.WT,Mapping.BNO-CSCcode,Mapping.EID
FROM Things INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode AND
Things.Description = Mapping.Description
...you should eliminate the duplication.
...你应该消除重复。
However, using a field that is obviously a text field in a join is not best practice, as if they are separately user-generated, they are unlikely to match, and it takes longer (relatively speaking) to match fields with a higher byte-count than those with low byte-counts.
但是，在联接中使用明显是文本字段的字段并不是最佳实践，因为它们好像是用户单独生成的，它们不太可能匹配，并且匹配具有更高字节的字段需要更长的时间（相对而言）计数比那些低字节数的人多。
