Условный запрос SQL-запросов

Вопрос

Со всем и всеми приведенными ниже примерами и данными есть ли другой способ сделать это, чтобы получить ожидаемые результаты, выполняющие UNION из разных таблиц?

Моя проблема

По сути, мне нужно иметь условную логику:

  1. если есть записи, найденные в течение определенного периода времени, то получить все записи (несколько) из таблицы

  2. если в течение определенного периода времени нет записей, то получите самую последнюю 1 запись из таблицы, так как до определенного периода времени

меры

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

Контекст и разъяснение

  • Возможно, используя таблицу temp / memory вместо переменных @Now~ и у меня есть TRUE условие UNION в коррелированной временной таблице или, может быть, какая-то сложная (или простая для некоторых) таблица данных методов JOIN ?

  • Я имею в виду « Определенный период времени » ниже или слова, которые в точности означают одно и то же, и этими словами я говорю о конкретном времени начала и конкретном времени окончания и все время между этими двумя точками – вот что я говорю когда я использую этот термин.

    • Существует система отчетности, которая генерирует эти начальные и конечные временные рамки, и я получил логику в хранимой процедуре, которая вызывается, и время даты передается.

      • Я только предоставил части хранимой процедуры и сделал несколько выборочных запросов из него, чтобы привести пример проблемы и ожидаемый или нужный результат.

Создание данных

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

Примечание. Прежде чем обязательно изменить <DBName> на фактическое имя или схему БД на вашем экземпляре MySQL.

 USE <DBName>; CREATE TABLE `ponumber` ( `TimeStr` datetime NOT NULL, `Value` int(11) NOT NULL, UNIQUE KEY `uk_Times` (`TimeStr`)); CREATE TABLE `batch_number` ( `TimeStr` datetime NOT NULL, `Value` int(11) NOT NULL, UNIQUE KEY `uk_Times` (`TimeStr`)); CREATE TABLE `batchweight` ( `TimeStr` datetime NOT NULL, `Value` int(11) NOT NULL, UNIQUE KEY `uk_Times` (`TimeStr`)); INSERT INTO `PONumber` (`TimeStr`,`Value`) VALUES ('2017-09-28 10:47:55',0); INSERT INTO `PONumber` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',1217911); INSERT INTO `PONumber` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:24:18',1217906); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:18',5522); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:25:33',5521); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 11:44:45',5520); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:05',5519); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:22:58',5518); INSERT INTO `batchweight` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:19',38985); INSERT INTO `batchweight` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',38985); INSERT INTO `batchweight` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:23:03',31002); 

Query One

 USE <DBName>; SET @bStartTime = '2017-09-29 11:10:00'; SET @bEndTime = '2017-09-29 12:48:00'; SELECT TimeStr, CONCAT('Set Load Number: ',Value) AS Value FROM ponumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime UNION SELECT TimeStr, CONCAT('Set Batch Number: ',Value) AS Value FROM batch_number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime UNION SELECT TimeStr, CONCAT('Batch Weight: ',Value) AS Value FROM batchweight WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr; 

Запрос одного результата

введите описание изображения здесь

Как вы можете видеть, нет PONumber таблице PONumber нет записей из времени, определяемого переменными @bStartTime и @bEndTime – установленным периодом времени.


Если в таблице PONumber в течение определенного периода времени нет записей данных, тогда результат должен PONumber самые последние записи из этой таблицы, даже те, которые выходят за определенный период времени, поэтому я построил Query Two .

Query Two

 USE <DBName>; SET @bStartTime = '2017-09-29 11:10:00'; SET @bEndTime = '2017-09-29 12:48:00'; SET @LastPONumber = (SELECT Value FROM PONumber ORDER BY TimeStr DESC LIMIT 1); SET @NowPONumber = (SELECT Value FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1); SET @LastPONumTimeStr = (SELECT TimeStr FROM PONumber ORDER BY TimeStr DESC LIMIT 1); SET @NowPONumTimeStr = (SELECT TimeStr FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1); SELECT DISTINCT TimeStr, Value FROM (SELECT CASE WHEN TimeStr = 1 THEN (SELECT @NowPONumTimeStr AS TimeStr) ELSE (SELECT @LastPONumTimeStr AS TimeStr) END AS TimeStr, CASE WHEN Value = 1 THEN (SELECT (CONCAT('Set Load Number: ',@NowPONumber)) AS Value) ELSE (SELECT (CONCAT('Set Load Number: ',@LastPONumber)) AS Value) END AS Value FROM PONumber) AS X UNION SELECT TimeStr, CONCAT('Set Batch Number: ',Value) AS Value FROM batch_number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime UNION SELECT TimeStr, CONCAT('Batch Weight: ',Value) AS Value FROM batchweight WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr; 

Запрос Два результата

введите описание изображения здесь

Итак, Query Two – это то, где я создал @LastPONumber , @NowPONumber , @LastPONumTimeStr и @NowPONumTimeStr и задал эти значения как результат запроса SELECT с использованием ORDER BY DESC и LIMIT 1 зависимости от условий с использованием выражений CASE WHEN .

Результаты теперь показывают самые последние PONumber таблицы PONumber так как ни один не был найден между определенным периодом времени, поэтому это позволило ему вернуться за пределы определенного периода времени.


Теперь существует требование, если в таблице Batch_Number в течение определенного периода времени нет записей данных, чтобы Batch_Number самую последнюю запись, как это было сделано с таблицей PONumber в Query Two , поэтому я построил Query Three, используя те же условия CASE WHEN логику и т. Д. , так как Query Two используется для таблицы PONumber .

Три запроса

 USE <DBName>; SET @bStartTime = '2017-09-29 11:10:00'; SET @bEndTime = '2017-09-29 12:48:00'; ## - PONumber Table Variables SET @LastPONumber = (SELECT Value FROM PONumber ORDER BY TimeStr DESC LIMIT 1); SET @NowPONumber = (SELECT Value FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1); SET @LastPONumTimeStr = (SELECT TimeStr FROM PONumber ORDER BY TimeStr DESC LIMIT 1); SET @NowPONumTimeStr = (SELECT TimeStr FROM PONumber WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1); ## - Batch_Number Table Variables SET @LastBatNumber = (SELECT Value FROM Batch_Number ORDER BY TimeStr DESC LIMIT 1); SET @NowBatNumber = (SELECT Value FROM Batch_Number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1); SET @LastBatNumTimeStr = (SELECT TimeStr FROM Batch_Number ORDER BY TimeStr DESC LIMIT 1); SET @NowBatNumTimeStr = (SELECT TimeStr FROM Batch_Number WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr DESC LIMIT 1); SELECT DISTINCT TimeStr, Value FROM (SELECT CASE WHEN TimeStr = 1 THEN (SELECT @NowPONumTimeStr AS TimeStr) ELSE (SELECT @LastPONumTimeStr AS TimeStr) END AS TimeStr, CASE WHEN Value = 1 THEN (SELECT (CONCAT('Set Load Number: ',@NowPONumber)) AS Value) ELSE (SELECT (CONCAT('Set Load Number: ',@LastPONumber)) AS Value) END AS Value FROM PONumber) AS X UNION SELECT DISTINCT TimeStr, Value FROM (SELECT CASE WHEN TimeStr = 1 THEN (SELECT @NowBatNumTimeStr AS TimeStr) ELSE (SELECT @LastBatNumTimeStr AS TimeStr) END AS TimeStr, CASE WHEN Value = 1 THEN (SELECT (CONCAT('Set Batch Number: ',@NowBatNumber)) AS Value) ELSE (SELECT (CONCAT('Set Batch Number: ',@LastBatNumber)) AS Value) END AS Value FROM Batch_Number) AS X UNION SELECT TimeStr, CONCAT('Batch Weight: ',Value) AS Value FROM batchweight WHERE TimeStr BETWEEN @bStartTime AND @bEndTime ORDER BY TimeStr; 

Запрос три результата

введите описание изображения здесь

Когда результат запроса Query Three обнаруживает, что таблица Batch_Number имеет записи между определенным периодом времени, она отображает только самую последнюю одну запись и не перечисляет два других значения номера партии ( 5521, and 5520 ), как показано в Query Two Result , Это, очевидно, связано с @NowBatNumber запроса @NowBatNumber содержащей только самую последнюю 1 запись.

Проблема

  • Мне нужно иметь возможность проверять записи Batch_Number за определенный период времени и выполнять одну из двух Batch_Number :

    1. Если существуют записи между этим временным периодом, перечислите все записи, а не только самую последнюю запись

    2. Если между указанным периодом времени не найдено записей, оглянитесь назад за пределами этого периода времени и перечислите самую последнюю одну запись

По существу для таблицы Batch_Number и того, как я пытался это сделать в Query Three, я ожидал, что результаты будут выглядеть точно так же, как и Query Two Result, и по-прежнему использовать условную логику, чтобы показать, что нужно в противном случае.

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

Краткие и простые вопросы, как правило, привлекают больше внимания, чем длинные / сложные. Это происходит не потому, что мы не можем ответить, но с таким количеством вопросов, и поэтому мало времени для добровольцев, трудно оправдать время для чтения больших вопросов.

Однако я думаю, что ваше основное требование не так сложно. Вам нужен способ извлечения строк, попадающих в промежуток времени, или если в этом диапазоне нет ближайших строк этого диапазона.

В базах данных, поддерживающих ROW_NUMBER () OVER (), это довольно просто (и MySQL 8.x планируется поддерживать это), но до этого времени для эмуляции row_number () вы можете использовать переменные и упорядоченный подзапрос.

Вы можете попробовать это решение здесь, в SQL Fiddle

MySQL 5.6 Настройка схемы :

 CREATE TABLE `ponumber` ( `TimeStr` datetime NOT NULL, `Value` int(11) NOT NULL, UNIQUE KEY `uk_Times` (`TimeStr`)); INSERT INTO `PONumber` (`TimeStr`,`Value`) VALUES ('2017-09-28 10:47:55',0); INSERT INTO `PONumber` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',1217911); INSERT INTO `PONumber` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:24:18',1217906); CREATE TABLE `batch_number` ( `TimeStr` datetime NOT NULL, `Value` int(11) NOT NULL, UNIQUE KEY `uk_Times` (`TimeStr`)); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:18',5522); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:25:33',5521); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-29 11:44:45',5520); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:05',5519); INSERT INTO `batch_number` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:22:58',5518); CREATE TABLE `batchweight` ( `TimeStr` datetime NOT NULL, `Value` int(11) NOT NULL, UNIQUE KEY `uk_Times` (`TimeStr`)); INSERT INTO `batchweight` (`TimeStr`,`Value`) VALUES ('2017-09-29 12:46:19',38985); INSERT INTO `batchweight` (`TimeStr`,`Value`) VALUES ('2017-09-28 06:26:07',38985); INSERT INTO `batchweight` (`TimeStr`,`Value`) VALUES ('2017-09-28 05:23:03',31002); 

Запрос :

 SET @bStartTime := '2017-09-29 11:10:00' SET @bEndTime := '2017-09-29 12:48:00' SELECT SrcTable, TimeStr, Value FROM ( SELECT @row_num :=IF( @prev_value=u.SrcTable, @row_num + 1 ,1) AS RowNumber , u.* , @prev_value := u.SrcTable FROM ( select 'ponumber' SrcTable , TimeStr, `Value` from ponumber union all select 'batch_number' SrcTable , TimeStr, `Value` from batch_number union all select 'batchweight' SrcTable , TimeStr, `Value` from batchweight ) u CROSS JOIN (SELECT @row_num := 1, @prev_value :='') vars ORDER BY SrcTable, TimeStr DESC ) d WHERE (d.TimeStr between @bStartTime and @bEndTime) OR (TimeStr < @bStartTime AND RowNumber = 1) 

Итак, что это такое, вычисляется «RowNumber», который начинается с 1 для самой последней строки для каждой исходной таблицы. Затем эта производная таблица фильтруется либо по временному диапазону, либо по номеру строки, если не в пределах временного диапазона.

Также обратите внимание, что я НЕ использовал UNION но вместо этого использовал UNION ALL . Существует большая разница в производительности и должна учиться использовать каждый в соответствии с потребностями. Если вы используете UNION , также не используйте select distinct потому что вы просто тратите усилия.

Результаты :

 | SrcTable | TimeStr | Value | |--------------|----------------------|-------| | batchweight | 2017-09-29T12:46:19Z | 38985 | | batch_number | 2017-09-29T12:46:18Z | 5522 | | batch_number | 2017-09-29T12:25:33Z | 5521 | | batch_number | 2017-09-29T11:44:45Z | 5520 | | ponumber | 2017-09-28T10:47:55Z | 0 |