表1:
user score
------------
A 1
B 2
表2:
user comment time
----------------------------
A good <timestamp 1>
A bad <timestamp 2>
B average <timestamp 3>
我想加入这两个表,以便获得以下信息:
user score comment
-------------------------
A 1 good
B 2 average
如您所见,我需要基于时间戳(最近的时间戳)加入第二张表的注释。我尝试过
SELECT st.user as user,st.score,case when v.comment is null then 'NA' else v.comment end as comment
FROM tale1
left JOIN (select distinct user,comment,max(time) from table2) v ON st.user=v.user
但这不起作用。