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 rn
    FROM 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_time
    FROM B
    GROUP 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 b1
    WHERE b1.update_time = (
        SELECT MAX(b2.update_time)
        FROM B b2
        WHERE 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