基于绝对值分组/连接行 || Oracle SQL

huangapple go评论46阅读模式
英文:

Grouping/Joining The Rows Based Based on Abs Amount || Oracle SQL

问题

我有一个名为XXTB_JE_TRX的源表,如下所示:

ENTITY HEADER_ID NET_AMT
301 10101 -30
301 10101 -50
301 10101 -20
401 10101 30
402 10101 50
302 10101 20

我想要输出(查找相关实体),如下所示:

ENTITY HEADER_ID NET_AMT RELATED_ENTITY
301 10101 -30 401
301 10101 -50 402
301 10101 -20 302
401 10101 30 301
402 10101 50 301
302 10101 20 301

这是我尝试的方法:

WITH t1 AS (
    SELECT
        entity,
        header_id,
        net_amt
    FROM
        XXTB_JE_TRX
    WHERE
        net_amt < 0
), t2 AS (
    SELECT
        entity,
        header_id,
        net_amt
    FROM
        XXTB_JE_TRX
    WHERE
        net_amt > 0
)
SELECT
    t1.entity,
    t1.header_id,
    t1.net_amt,
    t2.entity related_entity
FROM
    t1,
    t2
WHERE
        t1.header_id = t2.header_id
    AND abs(t1.net_amt) = abs(t2.net_amt)
UNION ALL
SELECT
    t2.entity,
    t2.header_id,
    t2.net_amt,
    t1.entity related_entity
FROM
    t1,
    t2
WHERE
        t1.header_id = t2.header_id
    AND abs(t1.net_amt) = abs(t2.net_amt);

这是一个可以工作的方法,但您是否可以在不使用UNION ALL的情况下实现这一目标呢?

英文:

I have a source table XXTB_JE_TRX like below:

ENTITY HEADER_ID NET_AMT
301 10101 -30
301 10101 -50
301 10101 -20
401 10101 30
402 10101 50
302 10101 20

and I want output (Find Related Entity) like this:

ENTITY HEADER_ID NET_AMT RELATED_ENTITY
301 10101 -30 401
301 10101 -50 402
301 10101 -20 302
401 10101 30 301
402 10101 50 301
302 10101 20 301

This is what I've tried:

WITH t1 AS (
    SELECT
        entity,
        header_id,
        net_amt
    FROM
        XXTB_JE_TRX
    WHERE
        net_amt &lt; 0
), t2 AS (
    SELECT
        entity,
        header_id,
        net_amt
    FROM
        XXTB_JE_TRX
    WHERE
        net_amt &gt; 0
)
SELECT
    t1.entity,
    t1.header_id,
    t1.net_amt,
    t2.entity related_entity
FROM
    t1,
    t2
WHERE
        t1.header_id = t2.header_id
    AND abs(t1.net_amt) = abs(t2.net_amt)
UNION ALL
SELECT
    t2.entity,
    t2.header_id,
    t2.net_amt,
    t1.entity related_entity
FROM
    t1,
    t2
WHERE
        t1.header_id = t2.header_id
    AND abs(t1.net_amt) = abs(t2.net_amt);

Is it a right way to do it? Can this be achieved without UNION ALL?

答案1

得分: 2

当然,这应该可以正常工作:

select a.entity_id, a.header_id, a.net_amt, b.entity_id as related_entity
  from xxtb_je_trx a join xxtb_je_trx b on a.header_id = b.header_id and a.net_amt = b.net_amt * -1;

如果不一定总会有related_entity,你可以使用外连接(outer join)。你还可以使用-b.net_amt代替b.net_amt * -1

英文:

Sure, this ought to work:

select a.entity_id, a.header_id, a.net_amt, b.entity_id as related_entity
  from xxtb_je_trx a join xxtb_je_trx b on a.header_id = b.header_id and a.net_amt = b.net_amt * -1;

You can do an outer join if there won't always be a related_entity. And you could do -b.net_amt instead of b.net_amt * -1.

huangapple
  • 本文由 发表于 2023年2月14日 05:20:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/75441289.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定