返回另一列中具有相同主键但不同值的记录

Question

我希望这不是一个很难的问题，希望我解释这个足以让人理解。在下面的查询中，我要做的是找到一个帐户(a.c acct_no)，其中帐户在ina表上有2条记录。其中一个acct_no的IRA_TYPE为'IR'，另一个账号的IRA_TYPE为'RH'。它们还需要具有相同的tax_id，这就是我将其与ACT_TABLE连接的原因。我能够使用此查询查找具有IR和RH的2个帐户的类似税务id，直到我降落在符合要求的两条记录上，但我试图查看如何重写此查询，仅返回一个帐户编码为"IR"和一个帐户编码为"RH"，其中帐户具有匹配的tax_ids。

SELECT a.acct_no, a.ira_type, b.tax_id
FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.acct_no, a.ira_type
--having count(*) > 1
order by tax_id

下面是我正在滚动的一个示例:

<表类>acct_noira_typetax_idtbody><<tr>48192627红外00100000117421898红外00110384621041289红外00110384632512838RH00110384655413417RH00110384665464636红外00110384652779378红外00173792253703374红外00213456740681537RH00294745469438955RH00441100083680957RH00471090964554609红外00732932136936217红外00867178470101808红外008958881

也许这个查询可以帮助:

SELECT b.tax_id, ir.acct_no as ir, rh.acc_no as rh
FROM ACT_TABLE b
LEFT JOIN INA ir ON b.acct_no = ir.acct_no AND ir.ira_type = 'IR'
LEFT JOIN INA rh ON b.acct_no = rh.acct_no AND rh.ira_type = 'RH'
WHERE ir.acct_no <> rh.acc_no

这个简单的改变可能是有用的

SELECT max(a.acct_no) as a.acct_no, a.ira_type, b.tax_id
FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.ira_type
order by tax_id

这可能会对你有所帮助:

SELECT a.acct_no, a.ira_type, b.tax_id FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.ira_type
order by tax_id
OR
SELECT Max(a.acct_no), a.ira_type, b.tax_id FROM INA a
inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type in ('IR', 'RH')
group by b.tax_id, a.ira_type
order by tax_id

with data as (
select b.tax_id, a.acct_no, a.ira_type,
min(a.ira_type) over (partition by b.tax_id) as mn,
max(a.ira_type) over (partition by b.tax_id) as mx,             
row_number() over (partition by b.tax_id, a.ira_type order by a.acct_no) as rn
from INA a inner join ACT_TABLE b
on a.acct_no = b.acct_no and a.ira_type in ('IR', 'RH')
)
select tax_id, acct_no, ira_type
from data
where mn <> mx and rn = 1 /* make sure both types are represented and keep just one */
order by tax_id, ira_type;

或者如果你想让它们放在同一行:

select ir.tax_id, ir.acct_no as ir_acct_no, rh.acct_no as rh_acct_no
from
(
select b.tax_id, min(a.acct_no) as acct_no, 'IR' as ira_type
from INA a inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type = 'IR'
group by b.tax_id
) as ir
inner join
(
select b.tax_id, min(a.acct_no) as acct_no, 'RH' as ira_type
from INA a inner join ACT_TABLE b on a.acct_no = b.acct_no
where a.ira_type = 'RH'
group by b.tax_id
) as rh
on ir.tax_id = rh.tax_id;

https://dbfiddle.uk/F3J588GF