基于PS_ITEM与PS_ITEM_XREF表的递归SQL数据查询需求

先明确你的两张表结构和现有数据：

PS_ITEM表结构与数据 #

PS_ITEM_XREF表结构与数据 #

你的需求是：查询PS_ITEM表中CLASS_NBR = 10146的所有行，同时要包含那些满足PS_ITEM.ITEM_NBR = PS_ITEM_XREF.ITEM_NBR_CHARGE且对应的PS_ITEM_XREF.ITEM_NBR_PAYMENT属于PS_ITEM表中CLASS_NBR = 10146的行（也就是ITEM_NBR=000000000000003这一行）。

实现的SQL语句 #

SELECT pi.COMMON_ID, pi.ITEM_NBR, pi.SEQ, pi.EMPLID, pi.ACCOUNT_NBR, pi.CLASS_NBR
FROM PS_ITEM pi
WHERE pi.CLASS_NBR = '10146'
UNION
SELECT pi.COMMON_ID, pi.ITEM_NBR, pi.SEQ, pi.EMPLID, pi.ACCOUNT_NBR, pi.CLASS_NBR
FROM PS_ITEM pi
JOIN PS_ITEM_XREF xref ON pi.ITEM_NBR = xref.ITEM_NBR_CHARGE
WHERE EXISTS (
    SELECT 1
    FROM PS_ITEM pi_pay
    WHERE pi_pay.ITEM_NBR = xref.ITEM_NBR_PAYMENT
      AND pi_pay.CLASS_NBR = '10146'
);

逻辑解释 #

1. 第一个SELECT部分直接获取PS_ITEM中CLASS_NBR为10146的所有行，这是你需求的基础部分。
2. 第二个SELECT部分通过关联PS_ITEM_XREF，找到那些作为收费项（ITEM_NBR_CHARGE）的PS_ITEM行，并且对应的支付项（ITEM_NBR_PAYMENT）存在于PS_ITEM的CLASS_NBR=10146的行中（这里就是ITEM_NBR=000000000000006，它的CLASS_NBR是10146，所以对应的收费项000000000000003会被选中）。
3. 使用UNION来合并两个结果集，自动去重（如果有重复行的话），确保最终结果符合你期望的输出。

执行这个SQL后，就能得到你想要的结果集：

内容的提问来源于stack exchange，提问作者Rohit Prasad