Блог

Главная - Вопросы по программированию - MySQL: выберите новейшие две строки для каждой группы

MySQL: выберите новейшие две строки для каждой группы

#mysql #sql

#mysql #sql

Вопрос:

У меня есть таблица, подобная этой:

 CREATE TABLE `data` (
  `id` int(11) NOT NULL,
  `deviceId` int(11) NOT NULL,
  `position_x` int(11) NOT NULL,
  `position_y` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `data`
  ADD PRIMARY KEY (`id`);
COMMIT;

id, deviceId, position_x, position_y
1   1           100         0  
2   2           150         50
3   3           200         20 
4   1           220         20
5   1           210         10
6   2           100         40
7   3           120         50
8   3           130         60
9   2           240         15

Мне нужны «самые новые» две строки для DeviceID, где больший идентификатор означает более новый.
Прямо сейчас я выбираю самую новую строку для каждого устройства с помощью этого запроса:

 SELECT
    id,
    deviceId,
    position_x, position_y
FROM data
WHERE deviceId > 0 AND
  id IN (SELECT MAX(id) FROM data GROUP BY deviceId)

И в цикле, где я вывожу данные, я выбираю вторую последнюю строку для каждого DeviceID в отдельном запросе, что довольно медленно / грязно:

 SELECT
    position_x
    position_y
FROM data
WHERE deviceId = :deviceId AND
    id < :id
ORDER BY id DESC
LIMIT 1

Есть ли способ объединить оба запроса или, по крайней мере, в одном запросе выбрать вторую строку для каждого идентификатора устройства из запроса 1?

Спасибо

Ответ №1:

Вы можете попробовать использовать row_number() 

 select * from
(
SELECT
    id,
    deviceId,
    position_x, position_y,row_number() over(partition by deviceid order by id desc) as rn
FROM data
WHERE deviceId > 0
)A where rn=2

Комментарии:

1. Я добавил `order by DeviceID DESC` в конце, и, похоже, я получаю только ОДНУ строку для идентификатора устройства, а не две строки. Похоже, это не работает:(

2. Если вам нужна только вторая строка, используйте where rn=2 . Если вы хотите использовать как первую, так и вторую строку, используйте where rn<=2

3. @тим, я думаю, ты получил ответ от Билла Карвина

4. Спасибо за запрос, выглядит отлично, не знал о rownumber и «over partition», прочитаю об этом 🙂

Ответ №2:

Для этого также можно использовать коррелированный подзапрос:

 SELECT d.*
FROM data d
WHERE d.deviceId > 0 AND
      d.id = (SELECT d2.id
              FROM data d2
              WHERE d2.deviceId = d.deviceId
              ORDER BY d2.id DESC
              LIMIT 1, 1
             );

С включенным индексом data(deviceId, id desc) вы можете быть впечатлены производительностью.