连接3个Oracle表的SQL语句

Question

我已经查看了几个网站和许多教程，试图确定最适合我的情况，虽然我找到了许多类似的可能的解决方案，但没有确切的解决方案给我我认为我需要的。每次尝试时，我要么返回不正确的数据，要么重复，要么无法返回所需的所有字段。任何帮助都是非常感谢的。谢谢，菜鸟

我有三个表，每个表共享一个primary_key of SUBR_ID。在外行术语中，我尝试从TBL_SUBR_INDV_CARRY_OVER中提取所有SUBR_ID和子INDV_ID (一对多)的GRP_ID = '0G0000000'，选择名字、姓氏、订户ID、个人ID和福利结转。下面是引用的三个表和尝试的语句。

TBL_SUBR_INDV_CARRY_OVER:
SUBR_ID
INDV_ID
BENEFIT_CARRY_OVER

TBL_SUBR_GRP:
SUBR_ID
GRP_ID

TBL_SUBR_INDV:
SUBR_ID
INDV_ID
LNME
FNME

Attempt#1

select DISTINCT DCS2000.TBL_SUBR_GRP.SUBR_ID, DCS2000.TBL_SUBR_INDV.INDV_ID, LNME, FNME, GRP_ID, BENEFIT_YEAR, BENEFIT_CARRY_OVER  
from DCS2000.TBL_SUBR_INDV_CARRY_OVER, 
     DCS2000.TBL_SUBR_GRP, 
     DCS2000.TBL_SUBR_INDV
where DCS2000.TBL_SUBR_INDV_CARRY_OVER.SUBR_ID = DCS2000.TBL_SUBR_INDV.SUBR_ID
     and DCS2000.TBL_SUBR_INDV.SUBR_ID = DCS2000.TBL_SUBR_GRP.SUBR_ID
     and DCS2000.TBL_SUBR_GRP.GRP_ID = '0G0000000'
     and DCS2000.TBL_SUBR_INDV_CARRY_OVER.BENEFIT_CARRY_OVER > '0'

Attempt#2

select DCS2000.TBL_SUBR_INDV.SUBR_ID, DCS2000.TBL_SUBR_INDV.INDV_ID, DCS2000.TBL_SUBR_INDV.LNME, DCS2000.TBL_SUBR_INDV.FNME, DCS2000.TBL_SUBR_GRP.GRP_ID, DCS2000.TBL_SUBR_INDV_CARRY_OVER.BENEFIT_YEAR, DCS2000.TBL_SUBR_INDV_CARRY_OVER.BENEFIT_CARRY_OVER  
from DCS2000.TBL_SUBR_INDV
join DCS2000.TBL_SUBR_GRP on ( 
     where DCS2000.TBL_SUBR_INDV_CARRY_OVER.SUBR_ID = DCS2000.TBL_SUBR_INDV.SUBR_ID
     and DCS2000.TBL_SUBR_INDV.SUBR_ID = DCS2000.TBL_SUBR_GRP.SUBR_ID
     and DCS2000.TBL_SUBR_GRP.GRP_ID = '0G0000000'
     and DCS2000.TBL_SUBR_INDV_CARRY_OVER.BENEFIT_CARRY_OVER > '0'

Attempt#3

 SELECT LNME, FNME, SUBR_ID, INDV_ID
 FROM DCS2000.TBL_SUBR_INDV
 WHERE DCS2000.TBL_SUBR_INDV.SUBR_ID IN
        (SELECT BENEFIT_CARRY_OVER
        FROM DCS2000.TBL_SUBR_INDV_CARRY_OVER
        WHERE DCS2000.TBL_SUBR_INDV_CARRY_OVER.SUBR_ID IN
            (SELECT SUBR_ID
            FROM DCS2000.TBL_SUBR_GRP
            WHERE DCS2000.TBL_SUBR_GRP.GRP_ID = '0G0000000')
    )  

Answer 1

我假设出现重复是因为单个人可能有许多结转记录。因此，请尝试：

select i.SUBR_ID, 
       i.INDV_ID, 
       max(i.LNME) LNME, 
       max(i.FNME) FNME, 
       max(g.GRP_ID) GRP_ID, 
       o.BENEFIT_YEAR, 
       sum(o.BENEFIT_CARRY_OVER) BENEFIT_CARRY_OVER
from DCS2000.TBL_SUBR_GRP g
join DCS2000.TBL_SUBR_INDV i
  on g.SUBR_ID = i.SUBR_ID
join DCS2000.TBL_SUBR_INDV_CARRY_OVER o
  on i.SUBR_ID = o.SUBR_ID and i.INDV_ID = o.INDV_ID and o.BENEFIT_CARRY_OVER > 0
where g.GRP_ID = '0G0000000' 
group by i.SUBR_ID, 
         i.INDV_ID, 
         o.BENEFIT_YEAR

请注意，单个表需要通过订阅者和单个ID连接到结转表；此外，数值字段(如BENEFIT_CARRY_OVER)的值周围不应该有引号。

Answer 2

问题是，由于是一对多的关系，所有三个表之间的简单连接将为您感兴趣的数据提供重复的行。连接必须为连接在一起的每一行创建一整行。而您想要的是单行。

我经常发现，构建一个复杂的查询最好是分部分完成

因此，首先查找具有感兴趣的值的所有ids，每个ids对应一行。由于我使用的键盘，我会在一定程度上缩短名称。

从grpid = 0000006的subrgrp中选择distinct subr_id

然后把这个和下一张表结合起来

Select distinct indvid subrid subrid in (Select distinct indvid subrgrp grpid = 0000006)

然后选择感兴趣的最后一行。

从subrindv where indvid中选择结转(Select distinct indvid subrid subrid in (选择distinct subr_id from subrgrp grpid = 0000006))

另一种方法是使用GROUP BY将多行分组为一行。