Как я могу переставить таблицу в MySQL?

Question

Это стол stop_times.txt где его формат (GTFS) выглядит примерно так:

+------------------+---------------+
|     trip_id      | stop_sequence |
+------------------+---------------+
| 4503599630773892 |      0        |
| 4503599630773892 |      1        |
|       ...        |      ...      |
| 4503599630773892 |      27       |
| 4503599630810392 |      0        |
| 4503599630810392 |      1        |
|       ...        |      ...      |
| 4503599630810392 |      17       |
| 4503599631507892 |      0        |
| 4503599631507892 |      1        |
|       ...        |      ...      |
| 4503599631507892 |      29       |
|       ...        |      ...      |
+------------------+---------------+

Мой ожидаемый результат:

+------------------+------------+-----------+
|     trip_id      | first_stop | last_stop |
+------------------+------------+-----------+
| 4503599630773892 |     0      |    27     |
| 4503599630810392 |     0      |    17     |
| 4503599631507892 |     0      |    19     |
|       ...        |    ...     |    ...    |
+------------------+------------+-----------+

PS: название может быть не точным. Пожалуйста, уточните это.

---

Еще один вопрос: как я могу добавить stop_name что соответствует stop_sequence к этому столу?

Вот неверный код по той причине, что имя остановки first_stop а также last_stop должны отличаться как соответствующие разным stop_id:

(SELECT routes.route_short_name, MIN(stop_times.stop_sequence) AS first_stop, stops.stop_name, MAX(stop_times.stop_sequence) AS last_stop, stops.stop_name
FROM stop_times
JOIN stops ON stops.stop_id=stop_times.stop_id
JOIN trips ON stop_times.trip_id=trips.trip_id 
JOIN routes ON routes.route_id=trips.route_id 
GROUP BY stop_times.trip_id);

---

РЕДАКТИРОВАТЬ: я делаю это после нескольких часов работы. Вот ключевой исходный код:

SELECT T1.trip_id, T1.stop_sequence, T1.stop_id, T2.stop_sequence, T2.stop_id
FROM
    -- create a new table T1: trip_id, stop_sequence=0, stop_id (first stop)
    (SELECT st_first1.trip_id, st_first1.stop_sequence, st_first1.stop_id
    FROM stop_times st_first1
    INNER JOIN 
        -- filter out the first stop: trip_id, stop_sequence=0
        (SELECT stop_times.trip_id, MIN(CAST(stop_times.stop_sequence AS UNSIGNED)) AS first_stop
        FROM stop_times
        GROUP BY stop_times.trip_id
        ) st_first2
    ON st_first1.trip_id=st_first2.trip_id AND st_first1.stop_sequence=st_first2.first_stop
    ) T1

LEFT JOIN -- combine T1 and T2

    -- create a new table T2: trip_id, stop_sequence=MAX, stop_id (last stop)
    (SELECT st_last1.trip_id, st_last1.stop_sequence, st_last1.stop_id
    FROM stop_times st_last1
    INNER JOIN
        -- filter out the last stop: trip_id, stop_sequence=MAX
        (SELECT stop_times.trip_id, MAX(CAST(stop_times.stop_sequence AS UNSIGNED)) AS last_stop
        FROM stop_times
        GROUP BY stop_times.trip_id
        ) st_last2
    ON st_last1.trip_id=st_last2.trip_id AND st_last1.stop_sequence=st_last2.last_stop
    ) T2

ON T1.trip_id=T2.trip_id

Answer 1

Вы можете GROUP BY trip_id а затем взять MIN а также MAXstop_sequence значения для получения первой и последней остановок соответственно.

SELECT DISTINCT st.trip_id, s.stop_name, t.first_stop, t.last_stop
FROM stop_times st INNER JOIN stops s
ON st.stop_id = s.stop_id
RIGHT JOIN
(
    SELECT trip_id, MIN(stop_sequence) AS first_stop, MAX(stop_sequence) AS last_stop
    FROM stop_times
    GROUP BY trip_id
) t
ON t.trip_id = st.trip_id

Answer 2

Если вы хотите создать из этого новую таблицу, вы можете использовать следующий запрос:

 create table new_table as
SELECT trip_id, MIN(stop_sequence) AS first_stop,MAX(stop_sequence) AS last_stop
FROM stop_times
GROUP BY trip_id