mysql - DB2 使用日期和时间显示用户的最新行记录

Question

我有 3 个用户的 100 条记录。我想显示每个用户的最新记录。我有以下疑问:

SELECT *  
FROM Mytable  
WHERE Dateabc = CURRENT DATE 
AND timeabc =  
(
  SELECT MAX(timeabc)
  FROM Mytable
)

它返回每个人的最新记录，我需要它返回每个用户的最新记录。

Answer 1

解决方案应该同时支持 DB2 和 mysql 吗？

SELECT * FROM Mytable as x
WHERE Dateabc = CURRENT_DATE 
  AND timeabc = (SELECT MAX( timeabc ) FROM Mytable as y where x.user = y.user)

如果只有 DB2 存在更高效的解决方案:

SELECT * from (
    SELECT x.*, row_number() over (partition by user order by timeabc desc) as rn 
    FROM Mytable as x
)
WHERE rn = 1