使用交叉联接时按语法分组

Anna Joel 发表于 Dev

安娜·乔尔

我希望在下面的SQL中应用“分组依据”条件，以便O / P在GP中显示POL＃。

select t.POl#, (DEB.CUSTD - CRED.CUSTC) AS GP 
from (
        (
            select POL.SP_NUM POL#
            ,      sum(D.AMT) AS CUSTD 
            from   S_INVOICE D
            ,      S_ASSET POL 
            where  POL.ROW_ID           =   D.FN_ACCNT_ID 
            and    POL.SP_NUM           in  ('000','111','222') 
            and    D.DEBIT_TYPE         =   'Customer' 
            group by POL.SP_NUM
        ) DEB
        CROSS JOIN
        (
            select pol.SP_NUM POL#
            ,      sum(C.AMT) AS CUSTC 
            from   S_SRC_PAYMENT C
            ,      S_ASSET POL
            where  POL.ROW_ID           =   C.ASSET_ID 
            and    POL.SP_NUM           in  ('000','111','222') 
            and    C.CG_DEDN_TYPE_CD    =   'Customer' 
            group by POL.SP_NUM
        ) CRED
    ) t
    group by t.POL#

当我执行相同的操作时，我得到“ ORA-00933：SQL命令未正确结束”错误，其中光标指向“ t”

请协助。

Expected O/P:
POL#    GP
000     800
111     120
222     50

附上样本数据并附上说明，以更好地理解需求：

表格1：

S_ASSET
ROW_ID    POL#
1         000
2         111
3         222
4         333
5         444

表2：

S_INVOICE (Debit Table)
FN_ACCNT_ID    POL#    DEBIT_TYPE          AMT
1              000     Customer            10
1              000     Customer            10
1              000     Insurer             5
2              111     Customer            10
3              222     Customer            10
3              222     Insurer             5
5              444     Insurer             10

表3：

S_SRC_PAYMENT (Credit Table)
ASSET_ID    POL#    CG_DEDN_TYPE_CD     AMT
1           000     Insurer             10
1           000     Insurer             10
1           000     Customer            5
2           111     Insurer             10
3           222     Insurer             5
3           222     Insurer             5
3           222     Customer            5
5           444     Customer            10

根据此查询，我将考虑按POL＃和AUM求和的“客户”记录。（客户的每笔借记都将按照POL＃向保险公司贷记，反之亦然）

每个POL＃对客户的预期O / P（借方总和-贷方总和）

POL #       AMT (GP)
000         15
111         10
222         5
333         0
444        -10

托斯滕·凯特纳

您显然只想按s_asset获取deb和cred，然后进行汇总以获得总和。您可以在没有联接的情况下执行此操作，而是直接对子和求子：

select 
  sp_num as pol#,
  sum(<get deb sum for the pol.row_id here>) - sum(<get cred sum for the pol.row_id here>)
from s_asset pol 
where sp_num in ('000','111','222')
group by sp_num;

完整的查询：

select 
  sp_num as pol#,
  coalesce(sum(
    (
      select sum(deb.amt)
      from s_invoice deb
      where deb.fn_accnt_id = pol.row_id
      and deb.debit_type = 'Customer' 
    )
  ), 0) -
  coalesce(sum(
    (
      select sum(cred.amt) 
      from s_src_payment cred
      where cred.asset_id = pol.row_id
      and cred.cg_dedn_type_cd = 'Customer' 
    ), 0)
  ) as gp
from s_asset pol 
where sp_num in ('000','111','222')
group by sp_num;

与join相同：

select 
  pol.sp_num as pol#,
  coalesce(sum(deb.total), 0) - coalesce(sum(cred.total), 0) as gp
from s_asset pol
left join
(
  select fn_accnt_id as pol_row_id, sum(deb.amt) as total
  from s_invoice
  where  debit_type = 'Customer' 
  group by fn_accnt_id 
) deb on deb.pol_row_id = pol.row_id
left join
(
  select asset_id as pol_row_id, sum(amt) as total
  from s_src_payment
  where  cg_dedn_type_cd = 'Customer' 
  group by asset_id
) cred on cred.pol_row_id = pol.row_id
group by pol.sp_num;