场景一 要实现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