我有一个功能查询,但是,我现在需要添加一个额外的列,该列将显示其中一个子Queries中的数据。我有点坚持如何做到这一点。
ive考虑将现有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
-
您可以使用内部加入而不是在子句中,然后从子查询中选择您需要的列
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;