Вложенный SELECT mySQL почему не работает?

Question

[romserg]

Здравствуйте.

Подскажите, почему выдаёт ошибку:

SELECT main.recdate,main.rectime,main.equip,main.reason,
 (SELECT m.recdate, m.rectime FROM main AS m WHERE (m.num > main.num) AND (m.equip=main.equip) AND (m.state='ПУСК') LIMIT 1)
  FROM main WHERE (state='СТОП')

Мне нужно найти запись с состоянием СТОП и добавить к ней поля из записи с состоянием ПУСК, к-ая идёт после СТОПовой записи (для того же equip, но с состоянием ПУСК). Т.е. запись, когда машина остановлена, дополнить полями, когда она запущена.

num recdate rectime equip state
1 2019-10-11 10:40:45 М1 СТОП
2 2019-10-11 10:42:46 М2 СТОП
3 2019-10-11 13:46:17 М3 СТОП
4 2019-10-12 08:10:32 М1 ПУСК
5 2019-10-12 09:45:40 М3 ПУСК

Надо, напр., для М3 выдать дату/время стопа и дополнить полями дата/время пуска.

Вложенный select с одним полем работает:

SELECT main.recdate,main.rectime,main.equip,main.reason,
 (SELECT m.recdate FROM main AS m WHERE (m.num > main.num) AND (m.equip=main.equip) AND (m.state='ПУСК') LIMIT 1)
  FROM main WHERE (state='СТОП')

Answer 1

[Adamos]

JOIN же.

Comments

[romserg]

А можно пример запроса? А то плохо разбираюсь в этих джойнах.

[Дмитрий]

romserg, https://aktual.com.ua/all/mysql-join/

[Adamos]

romserg,

SELECT m1.recdate, m1.rectime, m1.equip, m1.reason, m2.recdate, m2.rectime 
FROM main AS m1 
JOIN main AS m2 
ON m2.num > m1.num 
AND m2.equip = m1.equip 
AND m2.state = 'ПУСК' 
AND m1.state = 'СТОП'

[Сергей c0re]

Adamos, Все же думаю что наоборот надо

AND m2.state = 'ПУСК' 
AND m1.state = 'СТОП'

(СТОП же происходит после ПУСКа :)), а у вас m2.num > m1.num
так:

AND m1.state = 'ПУСК' 
AND m2.state = 'СТОП'

[romserg]

Сергей, в данном случае ПУСК происходит после СТОПа: сперва дата СТОПа, затем - дата последующего ПУСКа после СТОПа.

[Adamos]

Сергей, я же не сам придумывал условия, а переписал авторский запрос.

[romserg]

Adamos, т.к. здесь INNER JOIN, то - правильно я понимаю? - если после какого-то СТОПа нет последующего ПУСКа машины, то строчка с этим СТОПом выдана не будет?

[Adamos]

romserg, да. Если вам нужно, чтобы она была выдана с NULL во второй половине, смените на LEFT JOIN

[romserg]

Adamos, ещё вопрос. Если по условию для join будет найдено несколько строк, какая будет браться для вывода? Первая найденная? Есть в стандарте sql что-то на этот счёт?

[Adamos]

romserg, при джойне - каждая найденная. Если нужна только первая - нужно джойнить не с таблицей, а с выборкой.

[romserg]

Adamos, да мне нужна только первая строка из JOIN, т.е. только первый ПУСК после СТОПа. Adamos, можно примерчик?

[Adamos]

romserg, дайте угадаю: у вас может быть еще и несколько СТОПов и ПУСКов на каждый equip. То есть в каждой строке нужно будет искать не просто первый ПУСК, а первый ПУСК именно после этого конкретного СТОПа.
Боюсь, тут только полный JOIN, а потом группировка результатов по СТОПам и выборка из них нужных минимальных ПУСКов.

[Сергей c0re]

romserg, вопрос конкретизируйте...

[romserg]

Adamos, да, много стопов и пусков, после каждого стопа через какое-то время идёт пуск, это журнал учёта оборудования. И суть такая: выбрать каждый стоп у equip, а затем найти следующий сразу после него пуск для этого же equip. Как в начале и показывал:
num recdate rectime equip state
1 2019-10-11 10:40:45 М1 СТОП
2 2019-10-11 10:42:46 М2 СТОП
3 2019-10-11 13:46:17 М3 СТОП
4 2019-10-12 08:10:32 М1 ПУСК
5 2019-10-12 09:45:40 М3 ПУСК

Надо, напр., для М3 выдать дату/время стопа и дополнить полями дата/время пуска.

[Adamos]

romserg, лучше бы внести изменения в систему: добавить поле привязки, при записи каждого ПУСКа определять предыдущий СТОП и связывать их сразу, чтобы потом не искать.

[romserg]

Adamos, наверное, так и сделаю.
Но всё-таки даже теоретически интересует: есть ли возможность взять только одну (или N) строку из JOIN, какими путями это можно реализовать?

[Adamos]

romserg, вам надо не одну, а одну из.
У вас нет четкого условия, которое при сравнении двух строк в таблице может вам сказать без анализа других строк, что они друг другу подходят.

[romserg]

Условие таково, что нужна именно самая первая найденная join-ом строка. Т.е. после каждого стопа - ПЕРВАЯ после стопа запись по этой машине с пуском.

[Adamos]

romserg, я в курсе. Но сказать по одной строке, первая она или нет, невозможно. А JOIN работает именно с каждой строкой.

[romserg]

Adamos, тогда ближайший к СТОПу ПУСК по num (это PRIMARY KEY).

[Adamos]

romserg, и в третий раз пытаюсь донести до вас очевидное: ни по какому полю ОДНОЙ записи нельзя сказать, куда она ближайшая. Нужно сравнивать с другими записями. А джойн берет подряд по одной записи из каждой таблицы и больше ничего сравнивать не собирается.
Кабы у вас хотя бы equip был уникальным для каждого СТОПа - можно было бы джойнить с выборкой, сгруппированной по нему, и выбранным минимальным значением. Но у вас эта выборка получается уникальной для каждого СТОПа, джойн тут ничем не поможет. Разве что после него собрать все, что он наджойнил, и выбрать из этого нужное.

[romserg]

Вот такой вариант сработал:

SELECT m1.recdate,m1.rectime,m1.equip,m1.reason,m2.recdate,m2.rectime FROM main AS m1
 LEFT JOIN main AS m2 ON m2.num=(SELECT m3.num FROM main AS m3 WHERE (m3.num>m1.num) AND (m3.equip=m1.equip) AND (m3.state='ПУСК') ORDER BY m3.num LIMIT 1)
  WHERE (m1.state='СТОП')

[Adamos]

romserg, подзапрос на каждую строчку джойна. На сколько-нибудь большой таблице будет жрать катастрофически много времени и ресурсов.

[romserg]

Как можно оптимизировать?

[Adamos]

romserg, я выше уже говорил: джойнишь все ПУСКи позже этого СТОПа, группируешь результаты по конкретному СТОПу и выбираешь к нему самый ранний ПУСК.
А лучше, конечно, просто сделать связь еще во время записи и не мучить базу.

[romserg]

Adamos, я совсем запутался. Написал:

SELECT m1.recdate,m1.rectime,m1.equip,m1.reason,m2.recdate,m2.rectime,m2.downtime FROM main AS m1
 LEFT JOIN main AS m2 ON m2.num=MIN(m2.num) (m2.num>m1.num) AND (m2.equip=m1.equip) AND (m2.state='ПУСК') GROUP BY m2.num
  WHERE (m1.state='СТОП')

, но оно, конечно, не работает. Что нужно поправить?

[Adamos]

SELECT 
m1.recdate, m1.rectime, m1.equip, m1.reason, 
m2.recdate, m2.rectime, m2.downtime 
FROM main AS m1
LEFT JOIN main AS m2 
ON m2.num > m1.num AND m2.equip = m1.equip AND m2.state = 'ПУСК' AND  m1.state = 'СТОП'

Так вы получаете по каждому СТОПу все ПУСКи по этому оборудованию после него.
Осталось сгруппировать по СТОПам и выбрать первый же ПУСК.
У строк id есть? По нему можно и сгруппировать, и выбрать ближайший.

[romserg]

id строк - это поле num (PRIMARY KEY).
Ну GROUP BY m2.num, так?
А выбор ближайшего потом как? Просто после всего написать ORDER BY NUM LIMIT 1?

[Adamos]

SELECT MIN(m2.num) .... GROUP BY m1.num

[romserg]

ОК, вот так надо?:

SELECT 
m1.recdate, m1.rectime, m1.equip, m1.reason, 
m2.recdate, m2.rectime, m2.downtime 
FROM main AS m1
LEFT JOIN main AS m2 
ON SELECT MIN(m2.num) m2.num > m1.num AND m2.equip = m1.equip AND m2.state = 'ПУСК' AND  m1.state = 'СТОП' GROUP BY m1.num

Adamos, я просто никогда раньше не сталкивался с таким количеством условий в SQL, поэтому туплю. Не могли бы полностью прописать запрос sql?

[Adamos]

romserg, тут до полного запроса, который выдаст то, что вам нужно, еще как до Луны раком - даже если я правильно напишу этот, он всего лишь выдаст номер записи, а не ее содержимое. Сделайте уже нормальную связь между полями и не морочьте ни мне, ни себе голову.
Хотите поупражняться в зубодробительных запросах - вас ждет sql-ex.ru с подобными задачами.
На практике, правда, всегда оказывается лучше до такого не доводить, соблюдая KISS,