Группируйте данные с идентификатором и получайте данные из первой и последней строки

#mysql #sql #database #select #group-by

#mysql #sql #База данных #выберите #группировать по

Вопрос:

У меня есть таблица базы данных schedules , которая выглядит следующим образом:

Теперь я пытаюсь получить такие данные, как:

 ----------------------------------------------
| id | bus_id | route | dept_time | arr_time |
----------------------------------------------
| 1 |    1    |  1, 4 | 07:00:59  | 23:30:30 |
----------------------------------------------

route это просто набор station_id, который может быть проиндексирован с использованием, route_index. когда arr_time есть NULL , его среднее значение равно departing station и когда dept_time есть NULL , его среднее значение равно destination . Я сгруппировал маршрут с помощью этого запроса:

 SELECT id,bus_id,GROUP_CONCAT(station_id SEPARATOR ', ') AS route FROM schedules GROUP BY bus_id;

Но я не знаю, как получить arr_time и dept_time , используя этот запрос. Кроме того, как получить station имена вместо id в этом запросе. Station таблица содержит только (id and name) .

1. Если существует более одного маршрута, какой смысл иметь один arr_time и dept_time? Это не проблема SQL — данные результата вашего примера не имеют реального смысла.

2. @faraday703 это потому, что между ними может быть несколько остановок. например, от start до dest есть 2 остановки, которые будут упорядочены по индексу маршрута, а затем эти остановки могут иметь arr_time и depart_time.

3. Ваши данные не в «реляционном формате», например, отправление и прибытие в одну и ту же поездку разделены на две строки. Рассмотрите возможность использования электронной таблицы для исправления этого, например, формулы для просмотра строки выше. Однако ваш route столбец (разделенная запятыми строка station_id значений) снова нереляционный и вызовет у вас проблемы, когда вам позже понадобится запросить station_id значения.

4. @onedaywhen. привет, спасибо за ваш комментарий. как я уже говорил ранее, поездка будет разделена на множество остановок, поэтому я разделил строки. Есть ли, по вашему мнению, лучшее решение для этого? И я хочу, чтобы значение, разделенное запятой, отображалось в пользовательском интерфейсе, а не для дальнейшей обработки, поэтому я получаю данные в этом формате.

Ответ №1:

Вы можете использовать объединение для преобразования идентификаторов станций в их имена. Что касается времени прибытия и отправления — это грязный трюк, но поскольку агрегатные функции игнорируют null s, вы можете использовать min / max для их получения:

 SELECT   sch.bus_id,
         GROUP_CONCAT(st.name ORDER BY route_index SEPARATOR ', ') AS route,
         MIN(dept_time) AS dept_time,
         MAX(arr_time) AS arr_time
FROM     schedules sch
JOIN     stations st ON sch.station_id = st.id
GROUP BY bus_id;

1. Привет, спасибо за ваш ответ. Я получаю ошибку при ‘order by’. Если я удалю это, я получу результат, который я хочу. Не могли бы вы, пожалуйста, проверить, нет ли какой-либо ошибки. Спасибо.

2. @WaqarAhmed order by Предложение должно быть перед separator , виноват. Отредактировано и исправлено — спасибо, что заметили!