Daft SQL-вопрос. У меня есть таблица, подобная этой ( "pid" - 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)
;
Я хочу получить PID последней строки для каждого диска (1 результат на уникальный RID). Для данных образца я хотел бы:
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
и я получаю:
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 всегда является первым случаем PID для RID (используется строка /pid 1, используется первый раз 1, row/pid 3 при использовании RID 2 в первый раз, строка /pid 7 в первый раз удаляется 3). Хотя возврат максимальной временной отметки для каждого диска, pids - это не pids для временных меток из исходной таблицы. Какой запрос дал бы мне результаты, которые я ищу?
(Протестировано в 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)
select *
from (
select `pid`, `timestamp`, `cost`, `rid`
from theTable
order by `timestamp` desc
) as mynewtable
group by mynewtable.`rid`
order by mynewtable.`timestamp`
Надеюсь, я помог!
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
Я создал индекс для удаления и отметки времени.
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
Отображение строк 0 - 2 (всего 3 запросов, запросов 0,0104 сек)
Этот метод выберет все требуемые значения из theTable (test), левое соединение (maxt) на всех временных отметках выше, чем та, что была в тесте с тем же самым удалением. Когда метка времени уже самая высокая в тесте, нет совпадений в maxt - это то, что мы ищем - значения на maxt становятся NULL. Теперь мы используем предложение WHERE maxt.rid IS NULL или любой другой столбец на maxt.
У вас также могут быть такие подзапросы:
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
Но таким образом вам понадобится еще один подзапрос, если вы хотите, чтобы cost включался в показанные столбцы и т.д.
Таким образом, group by и join являются лучшим решением.
Если вы хотите избежать JOIN, вы можете использовать:
SELECT pid, rid FROM theTable t1 WHERE t1.pid IN ( SELECT MAX(t2.pid) FROM theTable t2 GROUP BY t2.rid);
Try:
select pid,cost, timestamp, rid from theTable order by timestamp DESC limit 2;