mysqlA表连接B表,并取B表中更新时间最新

场景一 要实现A表连接B表,并取B表中更新时间最新的那条记录,可以使用以下SQL查询:

方法一

SELECT A.*, B.*
FROM A 
LEFT JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY b_foreign_key ORDER BY update_time DESC) AS rnFROM B
) B ON A.id = B.b_foreign_key AND B.rn = 1

方法二

SELECT A.*, B.*
FROM A
LEFT JOIN B ON A.id = B.b_foreign_key
INNER JOIN (SELECT b_foreign_key, MAX(update_time) as max_update_timeFROM BGROUP BY b_foreign_key
) B_latest ON B.b_foreign_key = B_latest.b_foreign_key AND B.update_time = B_latest.max_update_time

方法三：在大数据集上性能欠缺

SELECT A.*, B.*
FROM A
LEFT JOIN (SELECT *FROM B b1WHERE b1.update_time = (SELECT MAX(b2.update_time)FROM B b2WHERE b2.b_foreign_key = b1.b_foreign_key)
) B ON A.id = B.b_foreign_key

场景二 取版本号最大的一整条信息

方法一：使用max函数。注意：版本号不是简单的数字，而是像 “v1.2.3” 这样的字符串， 需要使用适当的字符串比较函数或者转换函数。

SELECT *
FROM your_table
WHERE version = (SELECT MAX(version)FROM your_table
)
LIMIT 1

方法二：

SELECT *
FROM your_table
ORDER BY version DESC, update_time DESC
LIMIT 1