需要一个合适的MySQL查询 [英] Need an appropriate MySQL query
问题描述
很抱歉在这里提出这个问题，但是我对JOIN的理解很不稳定，而且几个小时的混乱使我无处可去.这是我设置的内容和所需的内容:
Apologies for asking this here, but my understanding of JOINs is rather shaky and a few hours of messing around hasn't led me anywhere. Here's what I have set up and what I need:
我有三个表，每个表分别用于用户，位置和签到.每次用户到某个位置时，他们都可以在该位置签到.检入表示例为:
I have three tables, one each for users, locations and checkins. Every time a user goes to a location, they can checkin there. A sample checkin table would be:
+------------+---------+-------------+---------------------+
| checkin_id | user_id | location_id | timestamp |
+------------+---------+-------------+---------------------+
| 18 | 99 | 1 | 2011-07-10 16:15:59 |
| 14 | 6 | 2 | 2011-07-10 04:49:53 |
| 17 | 6 | 5 | 2011-07-10 16:15:46 |
| 16 | 99 | 7 | 2011-07-10 16:14:00 |
| 19 | 99 | 2 | 2011-07-10 16:16:27 |
+------------+---------+-------------+---------------------+
如您所见，将有一个用户的多个实例和多个位置的实例.我需要弄清楚如何针对签到表中存在的每个用户找出他们最近一次在某处签到的时间.例如，在这种情况下，用户99的最近签入位于位置2(签入ID 19)，而用户6的最近签入位于位置5(签入ID 17).我只想要每个用户的最新签到位置.有没有办法我可以直接从MySQL获得此信息?如果可以，怎么办?
As you can see, there will be multiple instances of a user and multiple instances of locations. I need to figure out how to, for each user who exists in the checkin table, find out the most recent time they checked in somewhere. For instance, in this case, user 99's most recent checkin was at location 2 (checkin id 19) and user 6's most recent checkin was at location 5 (checkin id 17). I only want the most recent checkin for each user. Is there a way I can get this directly from MySQL? If so, how?
在此先感谢您的帮助. :)
Thanks in advance for your help. :)
推荐答案
SELECT *
FROM checkins AS c
JOIN (
SELECT user_id,MAX(`timestamp`) AS `timestamp`
FROM checkins
GROUP BY user_id
) AS x ON (x.user_id = c.user_id AND x.`timestamp`=c.`timestamp`);
要求提供的解释:
-
内部查询:
The inner query:
SELECT user_id,MAX(`timestamp`) AS `timestamp`
FROM checkins
GROUP BY user_id;
单独运行此查询以查看输出，但是它将为每个user_id选择最大时间戳值.然后，通过将其插入子选择中，对于我们的JOIN而言，我们将结果视为一个单独的表对待
Run this query alone to see the output, but it selects the maximum timestamp value for each user_id. Then, by plugging this into a sub-select, we're treating the results as though it were a separate table for the purpose of our JOIN
外部查询:
SELECT *
FROM checkins AS c
JOIN (...) AS x ON (x.user_id = c.user_id AND x.`timestamp`=c.`timestamp`);
这将别名为 c 的原始表 checkins 与第一个查询的结果(别名为 x .通过执行标准联接，仅显示两个表中都有结果的行-因此，我们有效地使用了联接来过滤掉不需要的行.
This joins the original table checkins with an alias of c (for easier typing) with the results from the first query, using the alias of x. By doing a standard join, only rows with results in both tables are shown--so we're effectively using the join to filter out the unwanted rows.
最终结果是，您将从内部查询中看到所有与user_id和MAX(timestamp)匹配的行.
The final result is that you'll see all of the rows that match the user_id and MAX(timestamp) from the inner query.
这篇关于需要一个合适的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
