找出两个结果集之间的重复项 [英] Single out duplicates between two result sets

问题描述

我要执行的查询遇到麻烦.我正在尝试选择出现在结果集中的值.

I'm having trouble with a query I'm trying to do. I'm trying to select values that come up in to result sets.

我目前正在处理两个查询:

I'm currently dealing with two queries:

A)

SELECT     /*+ RULE */ pi.compressed_name, pi.phn, to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth, gen.description as gender
        , to_char(pe.started_on , 'YYYY/MM/DD' ) as medicare_eligibility_start , to_char(pe.ended_on , 'YYYY/MM/DD' ) as medicare_eligibility_end
FROM    medcrtr.forest_node fnpppp,
    medcrtr.forest_node fnppp,
    medcrtr.forest_node fnpp,
    medcrtr.forest_node fnp,
    medcrtr.forest_node fn,
    medcrtr.group_member gm,
    medcrtr.group_type gt,
    medcrtr.program_eligibility pe,
    person_index pi,
    gender_type gen
WHERE   gm.entity_type_id = 1 --:P_PERSON_ENTITY_TYPE_ID
    AND gen.id = pi.gender_code
    AND gt.id = gm.group_id
    AND gt.category_id = 1 --icgroupmemebrcategory :P_GROUP_CATEGORY_ID
    AND fn.source_id = group_id
    AND fn.entity_type_id = 3 --icGOM:P_ENTITY_TYPE_ID
    AND fnp.id = fn.parent_id
    AND fnpp.id = fnp.parent_id
    AND fnppp.id = fnpp.parent_id
    AND fnpppp.id = fnppp.parent_id
    AND pe.person_id = gm.source_id
    AND pe.sub_program_id = fnpp.parent_id
    AND pi.person_id = gm.source_id
    AND fnppp.id = 1169
    AND (gm.ended_on >= SYSDATE OR gm.ended_on IS NULL)

B)除倒数第二行之外，与上面相同，

B) Same as above other than second last line, in which

AND fnppp.id = 1715

因此，现在第一个查询返回1536条记录，第二个查询返回2067条记录.但是，两个查询中都有记录(人员).我想做的就是将它们选出来. (基本上，我想做一个十字路口，所以我会调查一下)

So now the first query returns 1536 records, and the second returns 2067. There are however records(people) that come up in both queries. What I want to do is single them out. (Essentially I want to do an intersection so I"ll look into that)

到目前为止，我已经尝试执行以下操作:

So far I've tried to do the following:

-我向查询A添加了以下内容:

-I've added the following to query A:

AND pi.person_id NOT IN (SELECT    /*+ RULE */ pi.person_id FROM ... )

其中括号内的查询为查询B(第一行除外).返回大约10行(我不确定那是正确的).

where the query inside the brackets is query B (other than for the first line). This returns about 10 rows (I'm not sure that's accurate).

-然后我运行相同的查询，但使用NOT IN除外.那给了我大约200的结果(不确定这是否正确).

-I then ran the same query except with NOT IN. That gives me a result of about 200(not sure if this is accurate).

现在，这两个数字显然不等于1536，我认为应该发生这种情况?因此，显然其中一个或两个都是错误的.

Now those two numbers obviously don't add up to 1536, which I think should happen? So obviously one or both of them is wrong.

谁能告诉我我在做什么错?感谢您的帮助.

Can anyone tell what I'm doing wrong? Thanks for your help.

推荐答案

如果您确实要查找两个结果集之间匹配的记录，请查看有关

If you are truly looking for records that match between two result sets, check out information about the SQL INTERSECT command.

不过，查看您的查询，我不确定那不是您想要的.如果一条记录具有fnppp.id = 1169，那么同一条记录如何具有fnppp.id = 1715?

Looking at your query, though, I'm not sure that's what you want. If one record has fnppp.id = 1169, then how can the same record have fnppp.id = 1715?

您是否正在寻找更像 SQL UNION 的东西?

Are you possibly looking for something that's more like SQL UNION?

这篇关于找出两个结果集之间的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！