MySQL查询，MAX（）+ GROUP BY

本文介绍了MySQL查询，MAX（）+ GROUP BY的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Daft SQL问题。我有一个这样的表（'pid'是自动增量主col）

Daft SQL question. I have a table like so ('pid' is auto-increment primary col)

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;

实际表格资料：

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)
;

我想获得每个rid的最新行的PID（每个唯一RID一个结果）。对于示例数据，我想：

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:

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

我试过运行以下查询：

SELECT MAX(timestamp),rid,pid FROM theTable GROUP BY rid

b $ b

我得到：

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

返回的PID总是第一次出现RID的PID（row / pid 1 frst使用时间rid 1，行/ pid 3使用第一次RID 2，行/ pid 7是第一次使用rid 3）。虽然返回每个rid的最大时间戳，pids不是来自原始表的时间戳的pid。

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?

推荐答案

（在PostgreSQL 9.something中测试）

(Tested in PostgreSQL 9.something)

确定摆动和时间戳。

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

加入它。

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)

这篇关于MySQL查询，MAX（）+ GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！