获取数据的递归sql

mcvgt66p 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(354)

我有两个表ps\u item和ps\u item\u xref
ps\ U项目结构

COMMON_ID   ITEM_NBR        SEQ   EMPLID     ACCOUNT_NBR  CLASS_NBR
00000000200 000000000000002 1   00000000200 TUT001       10146
00000000200 000000000000002 2   00000000200 TUT001       10146
00000000200 000000000000002 3   00000000200 TUT001       10146
00000000200 000000000000006 1   00000000200 VAT001       10146
00000000200 000000000000008 1   00000000200 TUT001       10146
00000000200 000000000000003 1   00000000200 VAT001       
00000000200 000000000000004 1   00000000200 VAT001       
00000000200 000000000000009 1   00000000200 TUT001       10143
---------------------------------------------------------------

ps\项目\外部参照的结构

COMMON_ID    ITEM_NBR_CHARGE  ITEM_NBR_PAYMENT AMOUNT
   00000000200  000000000000003  000000000000006  2100
   00000000200  000000000000010  000000000000009  1000

我想从ps\u item中选择值，其中class\u nbr=10146，以及ps\u item.item\u nbr=ps\u item\u xref.item\u nbr\u为ps\u item中ps\u item\u xref.item\u nbr\u payment的所有行，其中class\u nbr=10146
所以我的结果是

COMMON_ID   TEM_NBR        SEQ   EMPLID     ACCOUNT_NBR  CLASS_NBR
    00000000200 000000000000002 1   00000000200 TUT001       10146
    00000000200 000000000000002 2   00000000200 TUT001       10146
    00000000200 000000000000002 3   00000000200 TUT001       10146
    00000000200 000000000000006 1   00000000200 VAT001       10146
    00000000200 000000000000008 1   00000000200 TUT001       10146
    00000000200 000000000000003 1   00000000200 VAT001

sql oracle Hierarchy recursion

来源：https://stackoverflow.com/questions/62977909/recursive-sql-to-get-data

1条答案

按热度按时间

dgiusagp1#

你是说这样的事吗？

SELECT * 
FROM   PS_ITEM 
WHERE  ( CLASS_NBR='10146' 
         OR 
         ITEM_NBR IN 
         ( SELECT  ITEM_NBR_CHARGE 
           FROM    PS_ITEM_XREF
           WHERE    ITEM_NBR_PAYMENT IN (SELECT ITEM_NBR FROM PS_ITEM WHERE CLASS_NBR='10146')))

赞(0）回复(0）举报 2021-07-24

我来回答

获取数据的递归sql

1条答案

相关问题

热门标签

最新问答