Получите по крайней мере последние 2 строки из каждой строки в объединенных таблицах mysql 5.X

ProgramBox

Получите по крайней мере последние 2 строки из каждой строки в объединенных таблицах mysql 5.X

Post author:admin
Запись опубликована:9 января, 2022
Post category:Вопросы по программированию

#mysql #join #limit #sql-limit

Вопрос:

У меня два столика:

Процессы

idПроцесс	данные
1	ХХХХ
2	ХХХХ
…	…

Трассировки:

idTrace	idПроцесс
1	1
2	1
3	1
4	2
5	2
6	2
7	2
…	…

Нужны последние два idTrace из каждого idProcess, упорядоченные по убыванию по idTrace:

idTrace	idПроцесс
3	1
2	1
7	2
6	2
…	…

РЕДАКТИРОВАТЬ можно строки в процессах, которые еще не существуют в трассировках…

1. Никогда не используйте id столбец для определения свежести записи. Существует множество причин, по которым идентификатор может быть выделен не по порядку. Идентификатор-это суррогатный ключ, его единственная обязанность-однозначно идентифицировать строку. Все остальное-это анти-паттерн. Всегда используйте другой столбец, такой как временная метка или другие данные, для упорядочения записи.

2. Это mysql 8 или 5.x? У каждого из них свои подходы.

3. Является mysql 5.x, трассировки имеют метку времени, спасибо за наблюдение!!

Ответ №1:

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

Схема (MySQL v5.5)

Следующий подход использует переменные для определения порядка и предложения where для ограничения порядковым номером строки.

 SET @row_num:=0;
SET @prev_grp:=NULL;

SELECT
     t.idTrace,
     t.idProcess  
FROM (
    SELECT 
        *, 
        @row_num:=(
             CASE 
                 WHEN @prev_grp<>idProcess THEN 1
                 ELSE @row_num 1
             END
        ) as rn,
        @prev_grp:=idProcess
    FROM 
        Tracings
    ORDER BY 
        idProcess,idTrace DESC
) t 
WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

или как один запрос

 SELECT
     t.idTrace,
     t.idProcess  
FROM (
    SELECT 
        *,
        @row_num:=(
             CASE 
                 WHEN @prev_grp<>idProcess THEN 1
                 ELSE @row_num 1
             END
        ) as rn,
        @prev_grp:=idProcess
    FROM 
        Tracings
    CROSS JOIN (SELECT @row_num:=0,@prev_grp:=NULL) as vars 
    ORDER BY 
        idProcess,idTrace DESC
) t 

WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

idTrace	idПроцесс
3	1
2	1
7	2
6	2

Просмотр на скрипке БД

Схема (MySQL v8.0)

Вы также можете использовать ROW_NUMBER для достижения этой цели, например.

 CREATE TABLE Processes (
  `idProcess` INTEGER,
  `data` VARCHAR(5)
);

INSERT INTO Processes
  (`idProcess`, `data`)
VALUES
  ('1', 'XXXX'),
  ('2', 'XXXXX');

CREATE TABLE Tracings (
  `idTrace` INTEGER,
  `idProcess` INTEGER
);

INSERT INTO Tracings
  (`idTrace`, `idProcess`)
VALUES
  ('1', '1'),
  ('2', '1'),
  ('3', '1'),
  ('4', '2'),
  ('5', '2'),
  ('6', '2'),
  ('7', '2');

Запрос № 1

 SELECT
     idTrace,
     idProcess
FROM (
    SELECT
         *,
         ROW_NUMBER() OVER (
             PARTITION BY idProcess
             ORDER BY idTrace DESC
         ) rn
    FROM
        Tracings
) t
WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

idTrace	idПроцесс
3	1
2	1
7	2
6	2

Запрос № 2 — если вам требуются данные из таблицы процессов

 SELECT
     t.idTrace,
     t.idProcess,
     p.data
FROM 
Processes p 
INNER JOIN (
    SELECT
         *,
         ROW_NUMBER() OVER (
             PARTITION BY idProcess
             ORDER BY idTrace DESC
         ) rn
    FROM
        Tracings
) t ON p.idProcess = t.idProcess
WHERE rn <=2
ORDER BY t.idProcess,t.idTrace DESC;

idTrace	idПроцесс	данные
3	1	ХХХХ
2	1	ХХХХ
7	2	XXXXX
6	2	XXXXX

Просмотр на скрипке БД Дайте мне знать, работает ли это для вас.

1. работает идеально, но не упоминайте, что это должно быть для mysql 5.x, извините и спасибо!!

2. @DiegoLaRosa Я обновил ответ с помощью аналогичного подхода, который работает с mysql 5.x и рабочей демонстрационной скрипкой бд. Дайте мне знать, если это сработает для вас.

3. Я отметил это в комментарии, который вы упомянули, в котором tracings была колонка с отметкой времени. Вы можете изменить подзапрос ответа mysql 5.x FROM Tracings ORDER BY idProcess,TimestampColumn DESC , чтобы приспособиться к этому.

Ответ №2:

MySQL5.x может использовать коррелированный подзапрос для поиска строк, к которым вы хотите присоединиться.

 SELECT
  *
FROM
  Processes   p
LEFT JOIN
  Tracings   t
    ON  t.idProcess = p.idProcess
    AND t.yourTimestampColumn >= (
      SELECT yourTimestampColumn 
        FROM Tracings
       WHERE idProcess = p.idProcess
    ORDER BY yourTimestampColumn DESC
       LIMIT 1 OFFSET 1
    )

Демо : https://www.db-fiddle.com/f/q1YCHFwX3zLiZ6xd52TdN6/0

Заслуга @ggordon, чью демо-версию я удочерил

1. Спасибо за ответ, в случае, если в трассировке есть только одна строка для процесса, мне нужна хотя бы эта строка. Я проверяю это в вашей скрипке и с помощью одной строки в трассировке по процессу показываю значения null.

Метки: Получите по крайней мере последние 2 строки из каждой строки в объединенных таблицах mysql 5.X