Mysql LEFT加入最近的记录

Question

我有这个查询：

SELECT A.ISIN, A.CUSIP, A.Currency,  A.StatedFinalMaturityDate, 
A.FirstPayDate, A.PaymentDelay, A.trancheName, A.DealTicker,
B.TrancheCoupon, B.OneMonthCoupon, B.SettleDate, B.Factor, 
B.paymentDate
from table1 A
left JOIN table2 B on (A.DealTicker = B.DealTicker AND A.trancheName=B.trancheName)
where A.ISIN IN ('XS2004372095','XS1679333432','XS0333340361',
'XS0333337813','XS0333324241','XS0333323862',
'XS0333323516','XS0333323193','XS0333316908',
'XS0333313988','USWFMW0N9Z05','USWFHZ5XIY05',
'USWFHVA6TK04','USWFHTXSQJ03');

这里，表1具有对应于每个ISIN的一个记录，但是表2可以具有多行。

表2有显示最新行的settleDate列。所以我只想从表2中选择最新的一行。由于表2需要两个JOINING列，我在这里有点困惑。

附言：我不能使用像row_number((这样的mysql 8功能，因为mysql服务器版本是5.7。我的桌子上有超过1.5亿张唱片。我尝试了一种方法，但由于数据量太大，没有成功。

Answer 1

您使用的是非常旧的MySQL版本，因此您不能使用windows函数，但必须访问同一个表两次。您可以使用NOT EXISTS或MAX来获取最后一行。以下是MAX:的示例

SELECT 
a.isin, a.cusip, a.currency,  a.statedfinalmaturitydate, a.firstpaydate,
a.paymentdelay, a.tranchename, a.dealticker, b.tranchecoupon,
b.onemonthcoupon, b.settledate, b.factor, b.paymentdate
FROM table1 a
LEFT JOIN
(
SELECT *
FROM table2
WHERE (dealticker, tranchename, settledate) IN
(
SELECT dealticker, tranchename, max(settledate)
FROM table2
GROUP BY dealticker, tranchename
)
) b ON b.dealticker = a.dealticker AND b.tranchename = a.tranchename
where a.isin IN ('XS2004372095','XS1679333432','XS0333340361','XS0333337813',
'XS0333324241','XS0333323862','XS0333323516','XS0333323193',
'XS0333316908','XS0333313988','USWFMW0N9Z05','USWFHZ5XIY05',
'USWFHVA6TK04','USWFHTXSQJ03');

Answer 2

您可以使用窗口函数：

SELECT A.ISIN, A.CUSIP, A.Currency,  A.StatedFinalMaturityDate, A.FirstPayDate, A.PaymentDelay, A.trancheName, A.DealTicker,
B.TrancheCoupon, B.OneMonthCoupon, B.SettleDate, B.Factor, B.paymentDate
FROM table1 A LEFT JOIN
(SELECT b.*,
ROW_NUMBER() OVER (PARTITION BY DealTicker, trancheName ORDER BY settleDate DESC) as seqnum
FROM table2 B
) B
ON A.DealTicker = B.DealTicker AND
A.trancheName = B.trancheName AND
B.seqnum = 1
WHERE A.ISIN IN ('XS2004372095','XS1679333432','XS0333340361','XS0333337813','XS0333324241','XS0333323862','XS0333323516','XS0333323193','XS0333316908','XS0333313988','USWFMW0N9Z05','USWFHZ5XIY05','USWFHVA6TK04','USWFHTXSQJ03');

在旧版本的MySQL中，您可以使用：

SELECT A.ISIN, A.CUSIP, A.Currency,  A.StatedFinalMaturityDate, A.FirstPayDate, A.PaymentDelay, A.trancheName, A.DealTicker,
B.TrancheCoupon, B.OneMonthCoupon, B.SettleDate, B.Factor, B.paymentDate
FROM table1 A LEFT JOIN
table2 B
ON A.DealTicker = B.DealTicker AND
A.trancheName = B.trancheName AND
B.settleDate = (SELECT MAX(b2.settleDate)
FROM table2 b2
WHERE b2.DealTicker = B.DealTicker AND
b2.trancheName = B.trancheName
)
WHERE A.ISIN IN ('XS2004372095','XS1679333432','XS0333340361','XS0333337813','XS0333324241','XS0333323862','XS0333323516','XS0333323193','XS0333316908','XS0333313988','USWFMW0N9Z05','USWFHZ5XIY05','USWFHVA6TK04','USWFHTXSQJ03');