#mysql #sql #group-by #sequence
Вопрос:
У меня есть следующая таблица, где состояние 0 для неактивного устройства и 1 для активного устройства.
CREATE TABLE `devices` (
`id` int NOT NULL AUTO_INCREMENT,
`state` int unsigned NOT NULL,
`dt` datetime NOT NULL,
`name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO devices (state,name,dt) VALUES (1,'Alfa','2021-10-12 11:30:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Alfa','2021-10-12 11:40:00');
INSERT INTO devices (state,name,dt) VALUES (1,'Alfa','2021-10-12 11:50:00');
INSERT INTO devices (state,name,dt) VALUES (1,'Alfa','2021-10-12 12:00:00');
INSERT INTO devices (state,name,dt) VALUES (1,'Beta','2021-10-12 11:30:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Beta','2021-10-12 11:40:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Beta','2021-10-12 11:50:00');
INSERT INTO devices (state,name,dt) VALUES (0,'Beta','2021-10-12 12:00:00');
http://sqlfiddle.com/#!9/8ea07de/3
Ожидаемым результатом запроса является:
Name State FROM TO
Alfa 1 '2021-10-12 11:50:00' '2021-10-12 12:00:00'
Beta 0 '2021-10-12 11:40:00' '2021-10-12 12:00:00'
В настоящее время я могу получить только столбцы «имя», «кому» и «состояние».
Можно ли найти запрос с ожидаемым результатом (столбец «от») для MySQL 5.6?
Ответ №1:
Вы можете использовать следующий запрос:
SET @state = -1; -- initial value not equal to real values
SET @dt = ''; -- initial value not equal to real dates
SELECT
name,
state,
from_dt,
dt
FROM (
-- get last dt by name
SELECT name, max(dt) dt
FROM devices
GROUP BY name
) last_state
JOIN (
SELECT
name,
state,
dt,
-- store dt eachtime when state changes
@dt := IF(@state <> state, dt, @dt) from_dt,
@state := state
FROM devices
ORDER BY name, dt ASC
) first_state using (name, dt)
;
Комментарии:
1. Это не дает ожидаемого результата для Alfa
2. Ты прав. Это немного усложняет сделать это, используя старую версию MySQL. здесь требуется использовать переменные . Я решил эту проблему и обновил ответ.
3. В MySQL 8.0 это может быть решено простым запросом с использованием оконных функций