sql - 从子查询返回多列数据
问题描述
我有一个功能查询,但是,我现在需要添加一个额外的列来显示来自其中一个子查询的数据。我有点坚持如何做到这一点。
我曾考虑将现有的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
--
解决方案
您可以使用 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;