MySQL 查询，MAX() + GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5657446/
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
MySQL query, MAX() + GROUP BY
提问by codinghands
Daft SQL question. I have a table like so ('pid' is auto-increment primary col)
愚蠢的 SQL 问题。我有一个像这样的表（'pid'是自动递增的主列）
CREATE TABLE theTable (
`pid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`cost` INT UNSIGNED NOT NULL,
`rid` INT NOT NULL,
) Engine=InnoDB;
Actual table data:
实际表数据：
INSERT INTO theTable (`pid`, `timestamp`, `cost`, `rid`)
VALUES
(1, '2011-04-14 01:05:07', 1122, 1),
(2, '2011-04-14 00:05:07', 2233, 1),
(3, '2011-04-14 01:05:41', 4455, 2),
(4, '2011-04-14 01:01:11', 5566, 2),
(5, '2011-04-14 01:06:06', 345, 1),
(6, '2011-04-13 22:06:06', 543, 2),
(7, '2011-04-14 01:14:14', 5435, 3),
(8, '2011-04-14 01:10:13', 6767, 3)
;
I want to get the PID of the latest row for each rid (1 result per unique RID). For the sample data, I'd like:
我想获取每个 RID 的最新行的 PID（每个唯一 RID 有 1 个结果）。对于示例数据，我想：
pid | MAX(timestamp) | rid
-----------------------------------
5 | 2011-04-14 01:06:06 | 1
3 | 2011-04-14 01:05:41 | 2
7 | 2011-04-14 01:14:14 | 3
I've tried running the following query:
我试过运行以下查询：
SELECT MAX(timestamp),rid,pid FROM theTable GROUP BY rid
and I get:
我得到：
max(timestamp) ; rid; pid
----------------------------
2011-04-14 01:06:06; 1 ; 1
2011-04-14 01:05:41; 2 ; 3
2011-04-14 01:14:14; 3 ; 7
The PID returned is always the first occurence of PID for an RID (row / pid 1 is frst time rid 1 is used, row / pid 3 the the first time RID 2 is used, row / pid 7 is first time rid 3 is used). Though returning the max timestamp for each rid, the pids are not the pids for the timestamps from the original table. What query would give me the results I'm looking for?
返回的 PID 始终是 RID 第一次出现的 PID（行/pid 1 是第一次使用rid 1，行/pid 3 是第一次使用RID 2，行/pid 7 是第一次使用rid 3 ）。尽管为每个rid 返回最大时间戳，但pid 不是原始表中时间戳的pid。什么查询会给我我正在寻找的结果？
回答by Mike Sherrill 'Cat Recall'
(Tested in PostgreSQL 9.something)
（在 PostgreSQL 9.something 中测试）
Identify the rid and timestamp.
确定rid 和时间戳。
select rid, max(timestamp) as ts
from test
group by rid;
1 2011-04-14 18:46:00
2 2011-04-14 14:59:00
Join to it.
加入它。
select test.pid, test.cost, test.timestamp, test.rid
from test
inner join
(select rid, max(timestamp) as ts
from test
group by rid) maxt
on (test.rid = maxt.rid and test.timestamp = maxt.ts)
回答by anzize
select *
from (
select `pid`, `timestamp`, `cost`, `rid`
from theTable
order by `timestamp` desc
) as mynewtable
group by mynewtable.`rid`
order by mynewtable.`timestamp`
Hope I helped !
希望我有所帮助！
回答by dkretz
SELECT t.pid, t.cost, to.timestamp, t.rid
FROM test as t
JOIN (
SELECT rid, max(tempstamp) AS maxtimestamp
FROM test GROUP BY rid
) AS tmax
ON t.pid = tmax.pid and t.timestamp = tmax.maxtimestamp
回答by Caio Iglesias
I created an index on rid and timestamp.
我在rid 和timestamp 上创建了一个索引。
SELECT test.pid, test.cost, test.timestamp, test.rid
FROM theTable AS test
LEFT JOIN theTable maxt
ON maxt.rid = test.rid
AND maxt.timestamp > test.timestamp
WHERE maxt.rid IS NULL
Showing rows 0 - 2 (3 total, Query took 0.0104 sec)
显示第 0 - 2 行（共 3 行，查询耗时 0.0104 秒）
This method will select all the desired values from theTable(test), left joining itself (maxt) on all timestamps higher than the one on test with the same rid. When the timestamp is already the highest one on test there are no matches on maxt - which is what we are looking for - values on maxt become NULL. Now we use the WHERE clause maxt.rid IS NULLor any other column on maxt.
此方法将从theTable(test) 中选择所有所需的值，并在所有时间戳高于测试中的时间戳上与相同的 rid 左连接 (maxt)。当时间戳已经是测试中的最高时间戳时，maxt 上没有匹配项 - 这就是我们正在寻找的 - maxt 上的值变为 NULL。现在我们maxt.rid IS NULL在 maxt 上使用 WHERE 子句或任何其他列。
回答by Nicolás Previale
If you want to avoid a JOIN, you can use:
如果要避免 JOIN，可以使用：
SELECT pid, rid FROM theTable t1 WHERE t1.pid IN ( SELECT MAX(t2.pid) FROM theTable t2 GROUP BY t2.rid);
回答by ypercube??
You could also have subqueries like that:
你也可以有这样的子查询：
SELECT ( SELECT MIN(t2.pid)
FROM test t2
WHERE t2.rid = t.rid
AND t2.timestamp = maxtimestamp
) AS pid
, MAX(t.timestamp) AS maxtimestamp
, t.rid
FROM test t
GROUP BY t.rid
But this way, you'll need one more subquery if you want costincluded in the shown columns, etc.
但是这样，如果您想cost包含在显示的列等中，您将需要另一个子查询。
So, the group byand joinis better solution.
所以，group byandjoin是更好的解决方案。
回答by vy32
Try:
尝试：
select pid,cost, timestamp, rid from theTable order by timestamp DESC limit 2;
