mysql - 将 sql 查询与另一个表连接起来

Question

我正在使用以下查询来获取用户的最新记录。

SELECT t1.username, t1.value, t1.date
FROM table t1
JOIN (select username, max(date) as maxdate from table
      group by username) t2 on t1.username = t2.username
                           and t1.date = t2.maxdate
WHERE t1.date >= CURDATE()
ORDER BY t1.date DESC

它返回一个具有以下结构的表

------------------------
username | value | date
------------------------

我有另一个具有以下结构的表 (t3)

----------------------------
username | category | group
----------------------------

如何得到类似如下表结构的结果

-------------------------------------------
username | value | date | category | group
-------------------------------------------

如何加入这些关系？

Answer 1

您可以通过以下方式加入:

SELECT t1.username, t1.value, t1.date, t3.category, t3.group
FROM table t1
JOIN (select username, max(date) as maxdate from table
      group by username) t2 on t1.username = t2.username
                           and t1.date = t2.maxdate
JOIN table3 t3 ON t3.username=t1.username
WHERE t1.date >= CURDATE()
ORDER BY t1.date DESC

提示:我不知道 mysql，但我认为它在 MS SQL 中是一样的。