英文:
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 < 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);
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
.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论