GROUP BY для непрерывных строк в SQL
Учитывая следующую таблицу:
ID State Date 12 1 2009-07-16 10:00 45 2 2009-07-16 13:00 67 2 2009-07-16 14:40 77 1 2009-07-16 15:00 89 1 2009-07-16 15:30 99 1 2009-07-16 16:00
Вопрос:
How can i GROUP by the field "State", while still maintaining the borders between the state changes?
SELECT MIN(ID) AS ID, State, MIN(Date) AS Date, COUNT(ID) AS Count
FROM table GROUP BY State
результаты в следующем:
ID State Date Count 12 1 2009-07-16 10:00 4 45 2 2009-07-16 13:00 2
but this is expected:
ID State Date Count 12 1 2009-07-16 10:00 1 45 2 2009-07-16 13:00 2 77 1 2009-07-16 15:00 3
Возможно ли это в SQL? Я пока не нашел решения ...