如何从表中显示重复项？

I have problem. I trying display duplicates from table. My code in pl sql

我有问题。我尝试从表中显示重复项。我在pl sql中的代码

SELECT intermediary_nr, beneficiary_role, contract_nr
  FROM (SELECT *
          from (select intermediary_nr,
                       beneficiary_role,
                       max(contract_nr) contract_nr
                  from boscs.atcs_commission_beneficiary
                 where beneficiary_role = 'LEAD'
                   and intermediary_nr is not null
                 group by intermediary_nr, beneficiary_role
                 ORDER BY dbms_random.value)

        union all
        SELECT *
          from (select intermediary_nr,
                       beneficiary_role,
                       max(contract_nr) contract_nr
                  from boscs.atcs_commission_beneficiary
                 where beneficiary_role = 'SUP_FOR_LEAD'
                   and intermediary_nr is not null
                 group by intermediary_nr, beneficiary_role
                 ORDER BY dbms_random.value)

        union all
        SELECT *
          from (select intermediary_nr,
                       beneficiary_role,
                       max(contract_nr) contract_nr
                  from boscs.atcs_commission_beneficiary
                 where beneficiary_role = 'COAGENT'
                   and intermediary_nr is not null
                 group by intermediary_nr, beneficiary_role
                 ORDER BY dbms_random.value))

Select intermediary_nr, beneficiary_role, contract_nt if a.contract_nr = b.contract_nr = c.contract_nr

如果a.contract_nr = b.contract_nr = c.contract_nr,请选择intermediary_nr,beneficiary_role,contract_nt

this relation me interested. I please about tips on how to solve this. Are there any ideas? I want to display only the information that is the same contract_nr for a, b, c. The rest do not. Does anyone know how to complete these questions?

这种关系让我感兴趣。我想知道如何解决这个问题。有什么想法吗?我想只显示与a,b,c相同的contract_nr信息。其余的没有。有谁知道如何完成这些问题?

1 个解决方案

#1

If I understand the request, you want to see only those rows that have the same contract in all three result sets. If that is the case, no problem.

如果我理解了请求,您只想查看在所有三个结果集中具有相同合同的那些行。如果是这样的话,没问题。

First, let's tighten up the query somewhat. You have seven(!) SELECT statements where only one is needed.

首先,让我们稍微收紧一下查询。你有七个(!)SELECT语句,只需要一个。

select  intermediary_nr, beneficiary_role, max(contract_nr) contract_nr
from    boscs.atcs_commission_beneficiary
where   beneficiary_role in( 'LEAD', 'SUP_FOR_LEAD', 'COAGENT' )
    and intermediary_nr is not null
group by intermediary_nr, beneficiary_role;

I've omitted the order by clause because it doesn't seem to serve any purpose.

我省略了order by子句,因为它似乎没有任何用途。

This will generate a result set kinda like this:

这将生成如下结果集:

intermediary_nr  beneficiary_role  contract_nr
===============  ================  ===========
            ...  ...               ...
             10  LEAD              100
             10  SUP_FOR_LEAD      100
             20  LEAD              101
             20  SUP_FOR_LEAD      101
             20  COAGENT           101
            ...  ...               ...

For the rest of this, I'm making the assumption that an output like the following is not possible:

对于其余部分,我假设不可能出现如下输出:

intermediary_nr  beneficiary_role  contract_nr
===============  ================  ===========
            ...  ...               ...
             10  LEAD              100
             10  SUP_FOR_LEAD      100
             20  LEAD              101
             20  SUP_FOR_LEAD      101
             20  COAGENT           101
             30  COAGENT           101
            ...  ...               ...

I don't know what your data looks like, so I don't know if that is a valid assumption. Only you can answer that.

我不知道你的数据是什么样的,所以我不知道这是否是一个有效的假设。只有你能回答这个问题。

Now you only want the rows where the same contract number appears for all three roles. In my example, that would be 101. If we feed the results of the first query into a second query, we can perform more grouping for that result:

现在,您只需要为所有三个角色显示相同合同号的行。在我的示例中,那将是101.如果我们将第一个查询的结果提供给第二个查询,我们可以为该结果执行更多分组:

select   contract_nr
from     <query above>
group by contract_nr
having count(*) = 3;

This will give you a list of the contracts you are looking for. Union those back to the first query and you have the result you are (I hope) looking for:

这将为您提供您正在寻找的合同列表。联合那些回到第一个查询,你有结果你(我希望)寻找:

with
ContractByRole( intermediary_nr,  beneficiary_role,  contract_nr )as(
  select  intermediary_nr, beneficiary_role, max(contract_nr) contract_nr
  from    boscs.atcs_commission_beneficiary
  where   beneficiary_role in( 'LEAD', 'SUP_FOR_LEAD', 'COAGENT' )
      and intermediary_nr is not null
  group by intermediary_nr, beneficiary_role
),
AllRoles( contract_nr )as(
  select   contract_nr
  from     ContractByRole
  group by contract_nr
  having count(*) = 3
)
select  cbr.*
from    ContractByRole cbr
join    AllRoles       ar
    on  ar.contract_nr = cbr.contract_nr;

which generates:

intermediary_nr  beneficiary_role  contract_nr
===============  ================  ===========
             20  LEAD              101
             20  SUP_FOR_LEAD      101
             20  COAGENT           101

If the intermediary value does not follow the pattern I show, the query will have to be tweaked a little to compensate, but that will not be difficult.

如果中间值不遵循我显示的模式,则必须稍微调整查询以进行补偿,但这并不困难。