SQL 从 LEFT OUTER JOIN 中删除重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2533310/
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
Remove Duplicates from LEFT OUTER JOIN
提问by Kaushik Gopal
My question is quite similar to Restricting a LEFT JOIN, with a variation.
我的问题与Restricting a LEFT JOIN非常相似，但有所不同。
Assuming I have a table SHOP and another table LOCATION. Location is a sort of child table of table SHOP, that has two columns of interest, one is a Division Key (calling it just KEY) and a "SHOP" number. This matches to the Number "NO" in table SHOP.
假设我有一张桌子 SHOP 和另一个桌子 LOCATION。Location 是表 SHOP 的一种子表，它有两列感兴趣的列，一个是 Division Key（简称为 KEY）和一个“SHOP”编号。这与表 SHOP 中的数字“NO”相匹配。
I tried this left outer join:
我试过这个左外连接：
SELECT S.NO, L.KEY
FROM SHOP S
LEFT OUTER JOIN LOCATN L ON S.NO = L.SHOP
but I'm getting a lot of duplicates since there are many locations that belong to a single shop. I want to eliminate them and just get a list of "shop, key" entries without duplicates.
但我得到了很多重复，因为有很多位置都属于一个商店。我想消除它们，只得到一个没有重复的“商店，钥匙”条目列表。
The data is correct but duplicates appear as follows:
数据正确，但重复出现如下：
SHOP KEY
1 XXX
1 XXX
2 YYY
3 ZZZ
3 ZZZ etc.
I would like the data to appear like this instead:
我希望数据显示如下：
SHOP KEY
1 XXX
2 YYY
3 ZZZ etc.
SHOP table:
商店表：
NO
1
2
3
LOCATION table:
位置表：
LOCATION SHOP KEY
L-1 1 XXX
L-2 1 XXX
L-3 2 YYY
L-4 3 YYY
L-5 3 YYY
(ORACLE 10g Database)
(ORACLE 10g 数据库)
回答by SoftwareGeek
You need to GROUP BY 'S.No' & 'L.KEY'
您需要按“S.No”和“L.KEY”分组
SELECT S.NO, L.KEY
FROM SHOP S
LEFT OUTER JOIN LOCATN L
ON S.NO = L.SHOP
GROUP BY S.NO, L.KEY
回答by Martin Smith
EDITFollowing the update in your scenario
编辑在您的场景中更新之后
I think you should be able to do this with a simple sub query (though I haven't tested this against an Oracle database). Something like the following
我认为您应该能够通过一个简单的子查询来做到这一点（尽管我还没有针对 Oracle 数据库对此进行过测试）。类似于以下内容
UPDATE shop s
SET divnkey = (SELECT DISTINCT L.KEY FROM LOCATN L WHERE S.NO = L.SHOP)
The above will raise an error in the event of a shop being associated with locations that are in multiple divisions.
如果商店与多个部门的位置相关联，则上述内容将引发错误。
If you just want to ignore this possibility and select an arbitrary one in that event you could use
如果您只想忽略这种可能性并在该事件中选择任意一个，您可以使用
UPDATE shop s
SET divnkey = (SELECT MAX(L.KEY) FROM LOCATN L WHERE S.NO = L.SHOP)
回答by woodge
I had this problem too but I couldn't use GROUP BY to fix it because I was also returning TEXT type fields. (Same goes for using DISTINCT).
我也有这个问题，但我无法使用 GROUP BY 来修复它，因为我也返回了 TEXT 类型的字段。（同样适用于使用 DISTINCT）。
This code gave me duplicates:
这段代码给了我重复：
select mx.*, case isnull(ty.ty_id,0) when 0 then 'N' else 'Y' end as inuse
from master_x mx
left outer join thing_y ty on mx.rpt_id = ty.rpt_id
I fixed it by rewriting it thusly:
我通过重写它来修复它：
select mx.*,
case when exists (select 1 from thing_y ty where mx.rpt_id = ty.rpt_id) then 'Y' else 'N' end as inuse
from master_x mx
As you can see I didn't care about the data within the 2nd table (thing_y), just whether there was greater than zero matches on the rpt_idwithin it. (FYI: rpt_idwas also not the primary key on the 1st table, master_x).
如您所见，我并不关心第二个表 ( thing_y) 中的数据，只关心其中的匹配项是否大于零rpt_id。（仅供参考：rpt_id也不是第一个表上的主键，master_x）。
