Блог

Итак, вот моя проблема: у меня есть следующая таблица :

 id | date       | am_in | pm_in
 1 | 2020-09-01 |     0 |     0
 2 | 2020-09-02 |     0 | 12345
 3 | 2020-09-03 | 12345 | 12345
 4 | 2020-09-04 |     0 | 12345
 5 | 2020-09-05 | 12345 |     0
 6 | 2020-09-06 | 12345 |     0


  

идентификатор уникален, но не имеет отношения к дате в фактической базе данных (идентификатор 101 не равен 100 дням после идентификатора 1)

Я должен находить каждые последовательные полдня с 12345 в am_in или pm_in. например :

 from 2020-09-02 pm to 2020-09-03 pm
from 2020-09-04 pm to 2020-09-05 am
from 2020-09-06 am to 2020-09-06 am
  

Пока мой запрос следующий :

 WITH t AS ( 
    SELECT `date` d,ROW_NUMBER() OVER(ORDER BY `date`) i 
    FROM test 
    where ( `am_in`=12345 or `pm_in`=12345) 
    and `date`>=CURDATE() 
    GROUP BY `date` 
    ) 
    SELECT MIN(d) as `from`,MAX(d) as `to` 
    FROM t 
    GROUP BY DATE_ADD(d, INTERVAL -i DAY) 
    having `to`>=CURDATE()
  

Это работало бы только с одной строкой, но в данном случае это не работает, потому что возвращает острова, где либо один из am_in, либо pm_in равен 12345.

 from 2020-09-02 to 2020-09-06
  

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

Приветствуется любая помощь или идея!

Заранее спасибо!

Отключите данные, а затем используйте пробелы и острова. Я бы посчитал половину дней с некоторой канонической даты:

 with cte as (
      select datediff(date, '2000-01-01') * 2 as n, date, am_in as i
      from t
      union all
      select datediff(date, '2000-01-01') * 2   1 as n, date, pm_in
      from t
     )
select min(date),
       (case when min(n) % 2 = 0 then 'am' else 'pm' end),
       max(date),    
       (case when max(n) % 2 = 0 then 'am' else 'pm' end)
from (select cte.*,
             row_number() over (order by n) as seqnum
      from t
      where i = 12345
     ) t
group by (n - seqnum)