sql - 如何在一个oracle表中查找多个对

Question

我在一个 Oracle 表中查找匹配项时遇到问题，希望您能帮助我。

我有一个包含预订数据的帐单表，如下所示:

ID  GROUP  Bill-Number  Value    Partner-Number
 1    1      111         10,90
 2    1      751         40,28
 3    1      438        125,60 
 4    1      659        -10,90        987 
 5    1      387       -165,88        755 
 6    1      774       -100,10 
 7    1      664        -80,12 
 8    1      259        180,22        999
 9    2      774       -200,10 
10    2      664        -80,12 
11    2      259        280,22        777

如您所见，我们有一些包含费用的账单。一段时间后，将出现总结先前成本的反法案。帐单和相关联的帐单的总和正在创建 0 的值。

示例:(id 2 + id 3 = id 5*-1) 的值或数字:40,28 + 125,60 + (-165,88) = 0

反账单包含“合作伙伴编号”。我需要将此信息添加到关联的账单中。

解决方案应该是这样的:

ID  GROUP  Bill-Number  Value    Partner-Number
 1    1      111         10,90        987
 2    1      751         40,28        755
 3    1      438        125,60        755
 4    1      659        -10,90        987 
 5    1      387       -165,88        755 
 6    1      774       -100,10        999
 7    1      664        -80,12        999
 8    1      259        180,22        999
 9    2      774       -200,10        777
10    2      664        -80,12        777
11    2      259        280,22        777

我必须只在一个组内匹配账单。 (ID是我的主键)只要该组包含一个与账单具有 1:1 关系的反账单，这对我来说是可行的。

但是我如何才能找到关系为 1:N 的第 1 组中的匹配项？ (该组包含多个反帐单)

我希望你能帮助我 - 提前谢谢你:)

Answer 1

以下 SQL 代码，分别在 Oracle 12c 和 18c 上进行了测试。思路/步骤:

{1} 将原始表拆分为一个减号表和加号表，只包含正数，这样可以节省我们稍后的一些函数调用。

{2} 创建 2 个 View ，它们将找到适合特定负号的正号组合(反之亦然)。

{3} 在名为 ALLCOMPONENTS 的表中以“逗号分隔”形式列出所有组件。

{4} 表 GAPFILLERS:扩展所有组件的(逗号分隔)ID，从而获得所有必要的值以填补原始表中的空白。

{5} LEFT JOIN 原始表到 GAPFILLERS。

原始表/数据

create table bills ( id primary key, bgroup, bnumber, bvalue, partner )
as
select 1, 1, 111, 10.90, null from dual union all
select 2, 1, 751, 40.28, null from dual union all
select 3, 1, 438, 125.60, null from dual union all 
select 4, 1, 659, -10.90, 987 from dual union all
select 5, 1, 387, -165.88, 755 from dual union all
select 6, 1, 774, -100.10, null from dual union all 
select 7, 1, 664, -80.12, null from dual union all 
select 8, 1,   259, 180.22, 999 from dual union all
select 9, 2,   774, -200.10, null from dual union all 
select 10, 2,   664, -80.12, null from dual union all 
select 11, 2,   259, 280.22, 777 from dual ;

{1} 将表格拆分为 PLUS 和 MINUS 表格

-- MINUSes
create table minuses as
select id
, bgroup       as mgroup
, bnumber      as mnumber
, bvalue * -1  as mvalue
, partner      as mpartner 
from bills where bvalue < 0 ;

-- PLUSes
create table pluses as
select id
, bgroup  as pgroup
, bnumber as pnumber
, bvalue  as pvalue
, partner as ppartner  
from bills where bvalue >= 0 ;

{2} View :查找 PLUSvalues 的组成部分

-- used here: "recursive subquery factoring" 
-- and LATERAL join (needs Oracle 12c or later)
create or replace view splitpluses
as
with recursiveclause ( nextid, mgroup, tvalue, componentid )
as (
  select                     -- anchor member
    id            as nextid
  , mgroup        as mgroup
  , mvalue        as tvalue  -- total value 
  , to_char( id ) as componentid
  from minuses
  union all
  select                     -- recursive member
    M.id
  , R.mgroup
  , R.tvalue + M.mvalue
  , R.componentid || ',' || to_char( M.id )
  from recursiveclause R
    join minuses M
      on M.id > R.nextid and M.mgroup = R.mgroup -- only look at values in the same group
)
--
select
  mgroup
, tvalue      as plusvalue
, componentid as minusids
, ppartner
from 
  recursiveclause R
, lateral ( select ppartner from pluses P where R.tvalue = P.pvalue ) -- fetch the partner id
where 
  tvalue in ( select pvalue from pluses where ppartner is not null ) -- get all relevant pvalues that must be broken down into components
  and ppartner is not null -- do this for all pluses that have a partner id
;

{2b} View :查找负值的分量

create or replace view splitminuses
as
with recursiveclause ( nextid, pgroup, tvalue, componentid )
as (
  select                     -- anchor member
    id            as nextid
  , pgroup        as pgroup
  , pvalue        as tvalue  -- total value 
  , to_char( id ) as componentid
  from pluses
  union all
  select                     -- recursive member
    P.id
  , R.pgroup
  , R.tvalue + P.pvalue
  , R.componentid || ',' || to_char( P.id )
  from recursiveclause R
    join pluses P
      on P.id > R.nextid and P.pgroup = R.pgroup
)
--
select
  pgroup
, tvalue      as minusvalue
, componentid as plusids
, mpartner
from 
  recursiveclause R
, lateral ( select mpartner from minuses M where R.tvalue = M.mvalue )
where 
  tvalue in ( select mvalue from minuses where mpartner is not null )
  and mpartner is not null
;

View 为我们提供了以下结果集:

SQL> select * from splitpluses;
MGROUP  PLUSVALUE  MINUSIDS  PPARTNER  
1       180.22     6,7       999       
2       280.22     9,10      777    

SQL> select * from splitminuses ;
PGROUP  MINUSVALUE  PLUSIDS  MPARTNER  
1       10.9        1        987       
1       165.88      2,3      755 

{3} 表 ALLCOMPONENTS:所有“组件”的列表

create table allcomponents ( type_, group_, value_, cids_, partner_ )
as
select 'components of PLUS' as type_, M.* from splitminuses M
union all
select 'components of MINUS', P.* from splitpluses P
;

SQL> select * from allcomponents ;
TYPE_                GROUP_  VALUE_  CIDS_  PARTNER_  
components of PLUS   1       10.9    1      987       
components of PLUS   1       165.88  2,3    755       
components of MINUS  1       180.22  6,7    999       
components of MINUS  2       280.22  9,10   777 

{4} 表 GAPFILLERS:派生自 ALLCOMPONENTS，包含我们需要填补原始表中“空白”的所有值。

-- One row for each CSV (comma-separated value) of ALLCOMPONENTS
create table gapfillers
as
select unique type_, group_, value_
, trim( regexp_substr( cids_, '[^,]+', 1, level ) ) cids_
, partner_
from (
  select type_, group_, value_, cids_, partner_
  from allcomponents
) AC 
connect by instr( cids_, ',', 1, level - 1 ) > 0
order by group_, partner_ ;

SQL> select * from gapfillers ;
TYPE_                GROUP_  VALUE_  CIDS_  PARTNER_  
components of PLUS   1       165.88  2      755       
components of PLUS   1       165.88  3      755       
components of PLUS   1       10.9    1      987       
components of MINUS  1       180.22  6      999       
components of MINUS  1       180.22  7      999       
components of MINUS  2       280.22  10     777       
components of MINUS  2       280.22  9      777       

7 rows selected.

{5} 最后的 LEFT JOIN

select
  B.id, bgroup, bnumber, bvalue
, case 
    when B.partner is null then G.partner_
    else B.partner
  end as partner
from bills B
  left join gapfillers G on B.id = G.cids_ 
order by 1 ; 

-- result
ID  BGROUP  BNUMBER  BVALUE   PARTNER  
1   1       111      10.9     987      
2   1       751      40.28    755      
3   1       438      125.6    755      
4   1       659      -10.9    987      
5   1       387      -165.88  755      
6   1       774      -100.1   999      
7   1       664      -80.12   999      
8   1       259      180.22   999      
9   2       774      -200.1   777      
10  2       664      -80.12   777      
11  2       259      280.22   777      


11 rows selected. 

DBFIDDLE here .