oracle 仅从两个表中选择匹配的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7115570/
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
select only matching records from two tables
提问by niceApp
I am using Oracle 10g Here is my scenario:
我正在使用 Oracle 10g 这是我的场景：
I have two tables
我有两张桌子
class1(groupName, subgroup)
class2(groupName, subgroup, ind)
Here is my data looks like:
这是我的数据看起来像：
class1
groupName subgroup
A 1
A 2
B 3
C 4
C 4
C 5
D 6
class2
groupName subgroup IND
A 1 Y
A 1 N
A 2 Y
A 2 N
B 3 Y
C 4 Y
C 4 N
Now, I need to get the data that has the matching groupName and subGroup in both class1 and class2(Not necessarily distinct match). In addition to that the IND column should have pair of 'Y' and 'N' value for each subgroup in class 2. Eg.In the above example GroupName A is qualified because A exists in both class1 and class2 and it has subgroup 1 and 2 exist in both class1 and class2 and IND column in class2 table has a pair of 'Y' and 'N' for each subgroup (i.e 1 and 2).
现在，我需要获取在 class1 和 class2 中具有匹配 groupName 和 subGroup 的数据（不一定是不同的匹配）。除此之外，IND 列应该为类 2 中的每个子组提供一对“Y”和“N”值。例如，在上面的示例中，GroupName A 是合格的，因为 A 存在于类 1 和类 2 中，并且它具有子组 1 和2 存在于 class1 和 class2 中，并且 class2 表中的 IND 列对于每个子组（即 1 和 2）都有一对“Y”和“N”。
Rest of the records are not qualified because : Group B has subgroup 3 that exists in both class1 and class2 but it does not have 'Y' and 'N' pairs for subgroup 3 in class2. Group C and D are not qualified because its all subgroup (4,5) doesn't exist in class2.
其余记录不合格，因为：B 组具有同时存在于 class1 和 class2 中的子组 3，但它在 class2 中没有子组 3 的“Y”和“N”对。组 C 和 D 不合格，因为它的所有子组 (4,5) 在 class2 中都不存在。
I have more than 700,000 records on both table class1 and class2. Anyone has any idea what is the effective way to get this information.
我在表 class1 和 class2 上都有超过 700,000 条记录。任何人都知道获取这些信息的有效方法是什么。
回答by bobs
Does this create what you need?
这是否创造了您需要的东西？
SELECT *
FROM class1 c1
JOIN class2 c2 ON c1.groupName = c2.groupName
AND c1.subgroup = c2.subgroup
WHERE
(
SELECT COUNT(DISTINCT ind)
FROM class2 c2a
WHERE c2a.groupName = c1.groupName
AND c2a.subgroup = c2a.subgroup
) = 2
AND
(
SELECT COUNT(DISTINCT subgroup)
FROM class1 c1b
WHERE c1b.groupName = c1.groupName
) =
(
SELECT COUNT(DISTINCT subgroup)
FROM class2 c2b
WHERE c2b.groupName = c2.groupName
)
回答by ThinkJet
Something like that must help ...
这样的事情必须帮助...
select
groupName
from (
select -- Get number of good subgroups for each group
groupName as groupName,
subGroupCount as subGroupCount,
sum( decode(ynCount, 2,1, 0) ) as goodGroupCount
from (
select -- Find which subgroups are good (contains both Y and N)
c1set.groupName as groupName,
c1set.subGroup as subGroup,
c1set.subGroupCount as subGroupCount,
count(distinct c2.IND) as ynCount
from
(
select -- Collect group/subgroup sets and get number of subGroups
distinct
c1.groupName as groupName,
c1.subGroup as subGroup,
count(distinct c1.subGroup)
over (parttition by c1.groupName) as subGroupCount
from
class1 c1
)
c1set,
class2 c2
where
c2.groupName (+) = c1set.groupName
and
c2.subGroup (+) = c1set.subGroup
group by
c1set.groupName,
c1set.subGroup,
c1set.subGroupCount
)
group by
groupName, subGroupCount
)
where
subGroupCount = goodGroupCount
Sorry, I can't test this code now by myself. Please comment if you found any inconsistency.
抱歉，我现在无法自己测试此代码。如果您发现任何不一致之处，请发表评论。
回答by Mikeb
If it doesn't matter that the IND column has Y and N specifically, you could do this:
如果 IND 列有 Y 和 N 无关紧要，您可以这样做：
select t1.groupName from
( select count(class1.groupName) a, groupName from class1 group By groupName) t1
inner join
( select count(class2.groupName) a, groupName from class2 group by groupName) t2
on t1.groupName = t2.groupName and 2*t1.a = t2.a
If it does matter, you could probably modify the second inner query like so:
如果确实重要，您可能可以像这样修改第二个内部查询：
select count(class2.groupName) a, groupName from class2 group by groupName
having max(ind) = 'Y' and min(ind) = 'N'
Edit to support additional tests mentioned in comments:
编辑以支持评论中提到的其他测试：
select distinct t1.groupName from
( select count(class1.groupName) a, groupName, subgroup from class1
group By groupName, subgroup) t1
inner join
( select count(class2.groupName) a, groupName, subgroup from class2
group by groupName, subgroup
having max(ind) = 'Y' and min(ind) = 'N') t2
on t1.groupName = t2.groupName and t1.subgroup = t2.subgroup and 2*t1.a = t2.a
