所以我有三个表:
Users
+-------+-----+----+
| id | val1|val2|
+-------+-----+----+
| 1 | 1 |3 |
| 2 | 2 |5 |
| 3 | 4 |7 |
+-------+-----+----+
UsersData
+----+--------------+------------+-----|
| id | users_id | created_at | gold|
+----+--------------+------------+-----|
| 9 | 1 |121454561212| 14 |
| 10| 1 |131454561212| 2 |
| 11| 2 |111454561212| 99 |
+----+--------------+------------+-----+
Extra
+----+------------+-----|
| id | users_id | val4|
+----+------------+-----|
| 1 | 1 | 5 |
| 2 | 1 | 6 |
| 3 | 1 | 7 |
+----+------------+-----+
因此,我希望(在单个查询中(为id=1的用户获得一个单行结果,该结果为:
- 用户表中的所有内容
- 该用户最近条目的黄金值(user_id=1,created_at=MAX(
- Extra表中的最大val4,其中user_id=1
所以结果行看起来像这样:
+-------+-----+----+-----+----+
| id | val1|val2|gold |val4|
+-------+-----+----+-----+----|
| 1 | 1 |3 | 2 | 7 |
------------------------------+
我可以用完成第一部分
SELECT Users.id, Users.val1, Users.val2, UsersData.gold
FROM UsersData
LEFT JOIN Users ON UsersData.users_id = Users.id
WHERE Users.id = 1
ORDER BY UsersData.created_at DESC
LIMIT 1
第二部分是
SELECT MAX(Distances.distance) AS maxdistance FROM Distances WHERE Distances.users_id = 1
但无论我怎么尝试,我都无法将它们结合在一起。。。我真的很想在单个查询中完成这项工作,显然我可以在多个查询中完成——但我相信这只是我缺乏mysql技能的问题。
谢谢!
只需使用子查询:
SELECT Users.id, Users.val1, Users.val2, UsersData.gold,
(SELECT MAX(Distances.distance) FROM Distances WHERE Distances.users_id = Users.id) AS maxdistance
FROM UsersData
RIGHT JOIN Users ON UsersData.users_id = Users.id
WHERE Users.id = 1
ORDER BY UsersData.created_at DESC
LIMIT 1
这是由Users.id:连接的子查询
SELECT MAX(Distances.distance) FROM Distances WHERE Distances.users_id = Users.id) AS maxdistance
我会使用这样的子查询:
select u.*,
(select ud.gold
from userdata ud
where ud.users_id = u.id
order by ud.created_at desc
limit 1
) as most_recent_gold,
(select max(e.val4)
from extra e
where e.users_id = u.id
) as max_val4
from users u
where u.id = 1 ;