从子查询返回多列数据

Question

我有一个函数式查询，但是，我现在需要添加一个额外的列来显示子查询中的数据。我有点卡住了，不知道该怎么做。

我曾考虑将现有的sql修改为join类型的查询，但没有成功。

--
select case when max(intid) != min(intid) then max(intid) end as INTID_NEW,min(intid) as INTID_PREV
from   DFUND
where   ANum in
    (select ANum from DFUND              
        where intid in
            (select intid as pair
            from AReview
            where Rule = 3366
            and trunc(RECVDDATE) >= trunc(sysdate-4) 
                        )
    )
group by ANum
order by ANum;
--

以下是当前查询返回的内容

 INTID_NEW INTID_PREV
---------- ----------
   4421156    3450805
   4426479    3174829

--

我希望它返回以下内容：

 INTID_NEW INTID_PREV RECVDDATE
---------- ---------- -----------
   4421156    3450805 01-MAY-2019
   4426479    3174829 04-MAY-2019

--

Answer 1

您可以使用INNER JOIN而不是IN子句，并从子查询中选择所需的列

    select case when max(intid) != min(intid) then max(intid) end as INTID_NEW, min(intid) as INTID_PREV, t.my_RECVDDATE 
    from   DFUND
    INNER JOIN (
        select ANum,  t2.my_RECVDDATE
        from D  
        INNER JOIN   (
          select intid , trunc(RECVDDATE) as my_RECVDDATE
                from AReview
                where Rule = 3366
                and trunc(RECVDDATE) >= trunc(sysdate-4) 
          ) t2 ON t2.intid =  DFund.intid
    ) t on t.ANum = DFUND.ANum 
    group by ANum,  t.my_RECVDDATE 
    order by ANum;