如何改写SQL查询以获取同税号下分别为IR和RH类型的账户对

看起来你想要筛选出那些同一个税号下同时存在IR类型和RH类型账户的记录，而不是把所有IR/RH账户都列出来。你的原查询只是返回了所有IR和RH的账户，但没有过滤掉那些只有单一类型的税号，所以需要调整一下逻辑。

这里有两种实用的方法可以实现你的需求：

方法一：先筛选符合条件的税号，再获取对应账户 #

这个思路是先找出那些同时包含IR和RH两种类型的税号，再用这些税号去匹配对应的账户记录，这样就能只保留你需要的结果：

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')
-- 子查询筛选出同时有IR和RH的税号
AND b.tax_id IN (
    SELECT b2.tax_id
    FROM INA a2
    INNER JOIN ACT_TABLE b2 ON a2.acct_no = b2.acct_no
    WHERE a2.ira_type IN ('IR', 'RH')
    GROUP BY b2.tax_id
    -- 确保该税号下至少有两种不同的IRA类型
    HAVING COUNT(DISTINCT a2.ira_type) = 2
)
ORDER BY b.tax_id, a.ira_type;

针对你提供的样本数据，这个查询会只返回tax_id = 001103846下的所有IR和RH账户，其他只有单一类型的税号（比如001000001）会被排除。

方法二：直接配对IR和RH账户（适合需要明确账户对的场景） #

如果你想直接看到每个税号下的IR账户和RH账户的配对关系，可以用自连接的方式，分别提取IR和RH的账户集合，再通过税号关联：

SELECT 
    ir.acct_no AS ir_account_number,
    rh.acct_no AS rh_account_number,
    ir.tax_id
FROM (
    -- 提取所有IR类型的账户及其税号
    SELECT a.acct_no, b.tax_id
    FROM INA a
    INNER JOIN ACT_TABLE b ON a.acct_no = b.acct_no
    WHERE a.ira_type = 'IR'
) ir
-- 通过税号关联对应的RH类型账户
INNER JOIN (
    SELECT a.acct_no, b.tax_id
    FROM INA a
    INNER JOIN ACT_TABLE b ON a.acct_no = b.acct_no
    WHERE a.ira_type = 'RH'
) rh ON ir.tax_id = rh.tax_id;

如果一个税号下有多个IR或多个RH账户，这个查询会返回所有可能的组合。如果只需要每个税号返回一组配对（比如取最小的IR账号和最小的RH账号），可以在子查询里加上GROUP BY tax_id并用MIN(acct_no)来聚合：

SELECT 
    ir.min_ir_acct AS ir_account_number,
    rh.min_rh_acct AS rh_account_number,
    ir.tax_id
FROM (
    SELECT MIN(a.acct_no) AS min_ir_acct, b.tax_id
    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
) ir
INNER JOIN (
    SELECT MIN(a.acct_no) AS min_rh_acct, b.tax_id
    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
) rh ON ir.tax_id = rh.tax_id;

为什么原查询没达到预期？ #

你的原查询只是过滤了IR和RH类型，并用tax_id排序，但没有筛选出那些同时包含两种类型的税号，所以会混入只有单一类型的记录。加上子查询的过滤逻辑后，就可以精准定位到你需要的税号和对应的账户了。

备注：内容来源于stack exchange，提问作者bkruep