本文介绍了如何通过MYSQL中的另一列来选择具有MAX(列值),PARTITION的行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的表格是:
id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 | borat | 600 8 | 13 | 01/01/2009 | borat | 700我需要选择每个具有 datetime 最大值的不同的 home .
I need to select each distinct home holding the maximum value of datetime.
结果将是:
id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 8 | 13 | 01/01/2009 | borat | 700我已经尝试过:
-- 1 ..by the MySQL manual: SELECT DISTINCT home, id, datetime AS dt, player, resource FROM topten t1 WHERE datetime = (SELECT MAX(t2.datetime) FROM topten t2 GROUP BY home) GROUP BY datetime ORDER BY datetime DESC不起作用.尽管数据库保留187个,但结果集有130行.结果包括 home 的一些重复项.
Doesn't work. Result-set has 130 rows although database holds 187. Result includes some duplicates of home.
-- 2 ..join SELECT s1.id, s1.home, s1.datetime, s1.player, s1.resource FROM topten s1 JOIN (SELECT id, MAX(datetime) AS dt FROM topten GROUP BY id) AS s2 ON s1.id = s2.id ORDER BY datetime不.给出所有记录.
-- 3 ..something exotic:具有各种结果.
推荐答案您是如此亲密!您需要做的就是选择住所及其最大日期时间,然后在两个字段上重新加入 topten 表:
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:
SELECT tt.* FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime更多推荐
如何通过MYSQL中的另一列来选择具有MAX(列值),PARTITION的行?
发布评论