如何引用和重用相关子查询？

Question

假设一个贸易表和一个通过交易id连接的cds_coupon表；优惠券表也有结算日期和结算金额。所需的查询是获取下一张要结算的息票的金额和日期。

    select t.tradeId AS tradeId,
(
  select settlement_date from cds_coupon e 
  where t.tradeId=e.tradeId 
  and e.settlement_date = (select min(settlement_date) from cds_coupon ei where ei.tradeId = t.tradeId  and ei.settlement_date > sysdate )
)  AS settlement_date,
( 
  select settlement_amount from cds_coupon e 
  where t.tradeId=e.tradeId 
  and e.settlement_date = (select min(settlement_date) from cds_coupon ei where ei.tradeId = t.tradeId and ei.settlement_date > sysdate )
)  AS settlement_amount, 
   FROM Trade t

可以看出，两个级别的相同相关性被执行两次，只是为了获取不同的字段-一次用于获取结算日期，另一次用于获取结算金额。因此出现了一个问题--如何引用和重用相关的子查询？

Answer 1

如果使用SQL Server，则可以使用Common Table Expression (CTE)。

;WITH SettlementDates ()
AS
(
    SELECT tradeId, MIN(settlement_date) as settlement_date
    FROM cds_coupon  
    WHERE settlement_date > sysdate
    GROUP BY tradeId
)

SELECT e.trade_id, e.settlement_date, e.settlement_amount
FROM cds_coupon e
JOIN SettlementDates sd 
    ON (e.trade_id = sd.trade_id)
   AND (e.settlement_date = sd.settlement_date)

Answer 2

使用联接而不是相关子查询：

select t.tradeId, e.settlement_date, e.settlement_amount
from Trade t
join (select ei.tradeId, ei.settlement_date, ei.settlement_amount
      from cds_coupon ei
      join (select tradeId, min(settlement_date) min_date
            from cds_coupon
            where settlement_date > sysdate
            group by tradeId) emin
      on ei.tradeId = e.tradeId and ei.settlement_date = min_date) e
on t.tradeId = e.tradeId

子查询是一种标准用法，用于查找包含最小或最大字段值的行。