MySQL 在 INNER JOIN 中使用 MAX - SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27991484/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using MAX within INNER JOIN - SQL
提问by Shivam Sachaphimukh
I have two tables, one called facebook_posts and the other called facebook_post_metrics.
我有两个表，一个叫做 facebook_posts，另一个叫做 facebook_post_metrics。
facebook_posts looks like
facebook_posts 看起来像
NAME id
a 1
b 1
c 4
d 4
facebook_post_metrics looks like
facebook_post_metrics 看起来像
number FBID Date_Executed User_Executed
1 1 2012-09-18 16:10:44.917 admin
2 1 2012-09-25 11:39:01.000 jeff
3 4 2012-09-25 13:20:09.930 steve
4 4 2012-09-25 13:05:09.953 marsha
So the common column that would be used for the inner join is id from the facebook_posts table and FBID from the facebook_post_metrics.
因此，用于内连接的公共列是来自 facebook_posts 表的 id 和来自 facebook_post_metrics 的 FBID。
So after the inner Join, the table should look like:
所以在内部 Join 之后，表格应该是这样的：
name number FBID Date_Executed User_Executed
a 1 1 2012-09-18 16:10:44.917 admin
b 2 1 2012-09-25 11:39:01.000 jeff
c 3 4 2012-09-25 13:20:09.930 steve
d 4 4 2012-09-25 13:05:09.953 marsha
However, I want to include another condition while doing this inner join. Basically, I just want to have the most updated entry for the joined table above. I know I would use max(date_executed) and then group it by FBID. But I'm not sure which part of the SQL Query that would go into when using INNER JOIN. Please help me out.
但是，我想在执行此内部连接时包含另一个条件。基本上，我只想为上面的连接表提供最新的条目。我知道我会使用 max(date_executed) 然后按 FBID 对其进行分组。但是我不确定在使用 INNER JOIN 时会进入 SQL 查询的哪一部分。请帮帮我。
Bottom line...I'd like to end up with a table looking like this:
底线......我想最终得到一个看起来像这样的表格：
name number FBID Date_Executed User_Executed
b 2 1 2012-09-25 11:39:01.000 jeff
c 3 4 2012-09-25 13:20:09.930 steve
采纳答案by AdamMc331
With a problem like this, I recommend breaking it down into pieces and putting it back together.
遇到这样的问题，我建议将其分解成碎片并重新组合在一起。
Finding the date of the most recent facebook_posts_metrics row is easy, like this:
查找最近 facebook_posts_metrics 行的日期很容易，如下所示：
SELECT fbid, MAX(date_executed) AS latestDate
FROM facebook_post_metrics
GROUP BY fbid;
So, to get the entire row, you want to join the original table with those results:
因此，要获取整行，您需要将原始表与这些结果连接起来：
SELECT fpm.*
FROM facebook_post_metrics fpm
JOIN(
SELECT fbid, MAX(date_executed) AS latestDate
FROM facebook_post_metrics
GROUP BY fbid) t ON t.fbid = fpm.fbid AND t.latestDate = fpm.date_executed;
Last, all you have to do is join that with facebook_posts table to get the name:
最后，您所要做的就是将其与 facebook_posts 表结合以获取名称：
SELECT fp.name, fpm.number, fpm.fbid, fpm.date_executed, fpm.user_executed
FROM facebook_posts fp
JOIN(
SELECT fpm.*
FROM facebook_post_metrics fpm
JOIN(
SELECT fbid, MAX(date_executed) AS latestDate
FROM facebook_post_metrics
GROUP BY fbid) t ON t.fbid = fpm.fbid AND t.latestDate = fpm.date_executed
) fpm ON fpm.fbid = fp.id AND fpm.date_executed = fp.updated_at;
Here is an SQL Fiddleexample.
这是一个SQL Fiddle示例。
EDIT
编辑
Based on your comments and looking over your design, I believe you can do something like this. First, get the latest facebook_post_metricswhich I have described above. Then, get the latest facebook_postby using a similar method. This searches the most recent updated_atvalue of the facebook post. If you want to use a different date column, just change that:
根据您的评论并查看您的设计，我相信您可以做这样的事情。首先，获取facebook_post_metrics我上面描述的最新版本。然后，facebook_post使用类似的方法获取最新的。这将搜索updated_atFacebook 帖子的最新值。如果您想使用不同的日期列，只需更改它：
SELECT fp.*
FROM facebook_posts fp
JOIN(
SELECT id, MAX(updated_at) AS latestUpdate
FROM facebook_posts
GROUP BY id) t ON t.id = fp.id AND t.latestUpdate = fp.updated_at;
Last, you can join that query with the one for facebook_post_metrics on the condition that the idand fbidcolumns match:
最后，您可以在id和fbid列匹配的情况下将该查询与 facebook_post_metrics 的查询连接起来：
SELECT fp.name, fpm.number, fpm.fbid, fpm.date_executed, fpm.user_executed
FROM(
SELECT fp.*
FROM facebook_posts fp
JOIN(
SELECT id, MAX(updated_at) AS latestUpdate
FROM facebook_posts
GROUP BY id) t ON t.id = fp.id AND t.latestUpdate = fp.updated_at) fp
JOIN(
SELECT fpm.*
FROM facebook_post_metrics fpm
JOIN(
SELECT fbid, MAX(date_executed) AS latestDate
FROM facebook_post_metrics
GROUP BY fbid) t ON t.fbid = fpm.fbid AND t.latestDate = fpm.date_executed) fpm
ON fp.id = fpm.fbid;
Here is an updated SQL Fiddleexample.
这是一个更新的SQL Fiddle示例。
回答by radar
you need a subquerythat calculated the maxusing group byand then join again with same tables to get all the details
您需要一个subquery计算max使用group by然后再次加入相同表以获取所有详细信息的
As per the latest edit, the update_at column is no longer there in posts as there are two entries, you can get only one by doing a group by
根据最新的编辑，update_at 列不再出现在帖子中，因为有两个条目，您只能通过分组获得一个
if you want all the entries then remove the aggregation and group byin the outer query.
如果您想要所有条目，则删除聚合并group by在外部查询中。
select max(fp.name), fpm.number, fpm.FBID, fpm.date_executed,
fpm.user_executed
from facebook_posts fp
join ( select max(Date_executed) dexecuted, FBID
from facebook_post_metrics
group by FBID
) t
on fp.id = t.fbid
join facebook_post_metrics fpm
on fpm.fbid = t.fbid
and fpm.date_executed = t.dexecuted
group by fpm.number, fpm.FBID, fpm.date_executed,
fpm.user_executed
回答by Spock
Untested, but you can do it by simply selecting the Max Date for the FBID in question. Something like this...
未经测试，但您只需选择相关 FBID 的最大日期即可。像这样的东西...
SELECT fb.name, fpm.number, fpm.FBID, fpm.date_executed, fpm.user_executed
FROM facebook_post_metrics fpm
join facebook_posts fb ON fpm.fbid = fb.id AND fb.updated_at = fpm.date_executed
WHERE fpm.Date_Executed = (
SELECT MAX(f.Date_executed)
FROM facebook_post_metrics f
WHERE f.FBID = fpm.fbid)
Here a fiddle http://sqlfiddle.com/#!2/1485d/15
