MySQL - показать пары записей в одной записи

У меня есть следующие данные в одной таблице:

Time, Type, Kilometers
12:00, 1, 0.1
12:30, 2, 0.2
14:00, 1, 0.4
15:00, 2, 1.0
16:00, 1, 1.2
16:30, 2, 1.5
16:45, 1, 2.0

Эти данные сортируются в хронологическом порядке с использованием поля DateTime. Я хотел бы показать эти "пары" записей как 1 строку, например:

StartTime, Type1Km, Type2Km
12:00, 0.1, 0.2
14:00, 0.4, 1.0
16:00, 1.2, 1.5
16:45, 2.0, NULL

Есть несколько предостережений: если нет Type1 для запуска, то показать NULL в поле Type1Km результирующих таблиц. Точно так же, если нет конца Type2, покажите NULL в поле Type2Km записей.

Как я мог это сделать?


mysql sql join
person Simon    schedule 11.08.2011    source источник
comment
Сложность заключается в том, что вы соединяете время с типом 1 со следующим временем типа 2. Если есть 2 типа 1 подряд, то Type2Km должен быть нулевым, верно?   -  person MPelletier    schedule 11.08.2011
comment
@MPelletier - правильно (это маловероятно, но было бы полезно, если бы решение учитывало это)   -  person Simon    schedule 11.08.2011
comment
Со мной было бы намного проще справиться, если бы в этой таблице был столбец с автоматическим приращением, чтобы связать X с X + 1 для дифференциального теста ... Есть ли такой, просто не представленный выше?   -  person DRapp    schedule 11.08.2011
comment
В дополнение к тому, что спросил @DRapp: будет ли это надежным? Будут ли времена вставлены последовательно?   -  person MPelletier    schedule 11.08.2011
comment
@DRapp, есть, однако нельзя гарантировать (и, скорее всего, не будет), что type2 = X+1   -  person Simon    schedule 11.08.2011
comment
@ Саймон, меня не волновал столбец «Тип», просто автоинкремент для ИТ-идентификатора был +1 к следующему идентификатору ...   -  person DRapp    schedule 11.08.2011
comment
Без идентификатора строки это будет какой-то очень странный коррелированный запрос внешнего соединения. Я даже не знаю, поддерживается ли это. Мой подход состоял бы в том, чтобы получить результаты и упорядочить их в коде: P   -  person MPelletier    schedule 11.08.2011
comment
@DRAPP - я понимаю, о чем вы спрашиваете, извините, я не объяснил лучше. К сожалению, идентификаторы в таблице не +1 от предыдущего идентификатора. Похоже, мне, возможно, придется создать временную таблицу для этого (или использовать код в моей среде IDE :))   -  person Simon    schedule 11.08.2011

Ответы (3)


arrow_upward
4
arrow_downward

К сожалению, в MySQL отсутствует FULL OUTER JOIN, так что вам придется UNION два набора вместе.

Это даст вам случаи, когда Type1Km существует, независимо от того, существует ли Type2Km.

SELECT
    t1.`Time` as StartTime,
    t1.`Kilometers` as Type1Km,
    t2.`Kilometers` as Type2Km
FROM `times` t1
LEFT JOIN `times` t2 ON t2.`Type` = 2
                    AND t2.`Time` = (SELECT `Time` FROM `times`
                                     WHERE `Time` > t1.`Time`
                                     ORDER BY `Time` LIMIT 1)
WHERE t1.`Type` = 1

Теперь нам нужны случаи, когда Type1Km не существует.

SELECT
    t2.`Time` as StartTime,
    NULL as Type1Km,
    t2.`Kilometers` as Type2Km
FROM `times` t2
LEFT JOIN `times` t1 ON t1.`Time` = (SELECT `Time` FROM `times`
                                     WHERE `Time` < t2.`Time`
                                     ORDER BY `Time` DESC LIMIT 1)
WHERE t2.`Type` = 2
  AND (t1.`Type` = 2 OR t1.`Type` IS NULL)

UNION вместе, и вы получите желаемый результат:

(
SELECT
    t1.`Time` as StartTime,
    t1.`Kilometers` as Type1Km,
    t2.`Kilometers` as Type2Km
FROM `times` t1
LEFT JOIN `times` t2 ON t2.`Type` = 2
                    AND t2.`Time` = (SELECT `Time` FROM `times`
                                     WHERE `Time` > t1.`Time`
                                     ORDER BY `Time` LIMIT 1)
WHERE t1.`Type` = 1

) UNION ALL (

SELECT
    t2.`Time` as StartTime,
    NULL as Type1Km,
    t2.`Kilometers` as Type2Km
FROM `times` t2
LEFT JOIN `times` t1 ON t1.`Time` = (SELECT `Time` FROM `times`
                                     WHERE `Time` < t2.`Time`
                                     ORDER BY `Time` DESC LIMIT 1)
WHERE t2.`Type` = 2
  AND (t1.`Type` = 2 OR t1.`Type` IS NULL)
)

ORDER BY `StartTime`

Обновить

В моем предыдущем запросе я забыл учесть наличие записи «типа 2» в самом начале. Обновлено с учетом этого. Вот результаты, которые я получаю:

Данные в таблице times:

+----------+------+------------+
| Time     | Type | Kilometers |
+----------+------+------------+
| 11:00:00 |    2 |        0.1 |
| 12:00:00 |    1 |        0.1 |
| 12:30:00 |    2 |        0.2 |
| 14:00:00 |    1 |        0.4 |
| 14:30:00 |    1 |        0.8 |
| 15:00:00 |    2 |        1.0 |
| 15:30:00 |    2 |        0.2 |
| 16:00:00 |    1 |        1.2 |
| 16:30:00 |    2 |        1.5 |
| 16:45:00 |    1 |        2.0 |
+----------+------+------------+

Результаты запроса:

+-----------+---------+---------+
| StartTime | Type1Km | Type2Km |
+-----------+---------+---------+
| 11:00:00  |    NULL |     0.1 |
| 12:00:00  |     0.1 |     0.2 |
| 14:00:00  |     0.4 |    NULL |
| 14:30:00  |     0.8 |     1.0 |
| 15:30:00  |    NULL |     0.2 |
| 16:00:00  |     1.2 |     1.5 |
| 16:45:00  |     2.0 |    NULL |
+-----------+---------+---------+
person Wiseguy    schedule 11.08.2011

arrow_upward
0
arrow_downward

Если вы можете, измените структуру таблицы, чтобы она имела type1km и type2km, а затем либо обновляйте при добавлении kms, либо просто суммируйте при выборе.

person njzk2    schedule 11.08.2011

arrow_upward
0
arrow_downward

Это может сработать

select 
max(Time), 
substring_index(group_concat(if(type=2,null,Kilometers) order by type),',',1),
substring_index(group_concat(if(type=1,null,Kilometers) order by type desc),',',1) 
from your_table 
group by date_format(Time, '%H');

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

Чтобы лучше объяснить, что делает этот забавный запрос

group_concat(if(type=2,null,Kilometers) order by type
-- this is ensure the Kilometers must NOT from type=2

group_concat(if(type=1,null,Kilometers) order by type desc
-- this is ensure the Kilometers must NOT from type=1
person ajreal    schedule 11.08.2011