Помогите написать запрос: путаница в порядке операций GROUP BY и ORDER BY
У меня есть таблица с именем Информация об этой схеме:
int objectId;
int time;
int x, y;
В системе много избыточных данных - то есть objectId НЕ УНИКАЛЬНЫЙ. Для каждого objectId может быть несколько записей time, x, y.
Я хочу получить список последней позиции каждого объекта. Я начал с этого запроса:
SELECT * FROM Info GROUP BY objectId
Это дало мне именно тот список, который я искал. Однако я также хочу получить только последнее время для каждого объекта, поэтому я попробовал:
SELECT * FROM Info GROUP BY objectId ORDER BY time DESC
Это дало мне список информации по нисходящей системе time. Однако Я не думаю, что он сделал то, что я хочу - это вернуть мне последний time, x, y для каждого объекта..
Кто-нибудь может представить себе запрос, который сделает то, что я хочу?
Обновлять Я попробовал три лучших решения, чтобы увидеть, как они работают друг с другом на наборе данных, состоящем из примерно 50 000 Infos. Вот результаты:
-- NO INDEX: forever
-- INDEX: 7.67 s
SELECT a.*
FROM Info AS a
LEFT OUTER JOIN Info AS b ON (a.objectId = b.objectId AND a.time < b.time)
WHERE b.objectId IS NULL;
-- NO INDEX: 8.05 s
-- INDEX: 0.17 s
select a.objectId, a.time, a.x, a.y
from Info a,
(select objectId, max(time) time from Info group by objectId) b
where a.objectId = b.objectId and a.time = b.time;
-- NO INDEX: 8.30 s
-- INDEX: 0.18 s
SELECT A.time, A.objectId, B.x, B.y
FROM
(
SELECT max(time) as time, objectId
FROM Info
GROUP by objectId
) as A
INNER JOIN Info B
ON A.objectId = b.objectId AND A.time = b.time;
С некоторым запасом where превосходит inner join.
Ответы 4
SELECT A.time, A.objectID, B.X, B.Y
FROM
(
SELECT max(time) as time, objectID
FROM table
GROUP by objectID
) as A
INNER JOIN table B
ON A.objectID = b.objectID AND A.Time = b.Time
votenaders, решение не сработает, если x и y уменьшатся в любой точке временной шкалы.
Один из способов - использовать подзапрос.
select distinct a.objectID, a.time, a.x, a.y
from Info a,
(select objectID, max(time) time from Info group by objectID) b
where a.objectID = b.objectID and a.time = b.time
Обновлено: добавлено DISTINCT для предотвращения дублирования строк, если один objectId имеет несколько записей с одинаковым временем. В зависимости от ваших данных, если это необходимо, автор вопроса упомянул, что было много повторяющихся строк. (добавлено Томалак)
Мне больше нравится стиль SQL Glomek, чем стиль Кэмпбелла, поэтому я отдаю свой голос именно ему, но в основном они одинаковы. Дело вкуса.
Томалак: Вы можете подробнее остановиться на этом? Можете ли вы взять SQL-код Glomek и добавить предложение DISTINCT? Или, может быть, Гломек не будет возражать?
Сделанный. Думаю, написание собственного ответа уже не будет иметь большого значения. :-)
Как бы то ни было, вот еще один способ получить желаемый результат. Я привык делать подобные трюки еще во времена MySQL 4.0, еще до того, как стали поддерживаться подзапросы.
SELECT a.*
FROM Info AS a
LEFT OUTER JOIN Info AS b ON (a.objectID = b.objectID AND a.time < b.time)
WHERE b.objectID IS NULL;
Другими словами, покажите мне строку, в которой нет другой строки с тем же objectID и большим временем. Это, естественно, возвращает строку с максимальным временем для каждого идентификатора объекта. GROUP BY не требуется.
Разве тебе там не нужен МАКС?
Нет, вам не нужен MAX (). Когда b.objectID IS NULL, это означает, что условие соединения не было выполнено, что означает, что текущая строка 'a' имеет максимальное значение времени любой строки с тем же objectID.
Это довольно распространенный способ получить всю информацию в строке для строки, которая является частью группы.
Select Info.*
from Info
inner join
(select ObjectId, max(time) as Latest
from Info
group by ObjectId) I
on Info.ObjectId = I.ObjectID and Info.time = I.Latest
Один и тот же вопрос задавали в разных формах пару раз за последние пару недель. Я забыл, как были сформулированы вопросы.
Использование предложения DISTINCT предотвращает дублирование строк в выходных данных, если существует более одной записи с одинаковым временем для одного objectId.