SQL-запрос, объединяющий несколько таблиц (MySQL)

Мне нужна "небольшая" помощь с запросом SQL (MySQL).

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

COURIERS таблица:

    +------------+
    | COURIER_ID |
    +------------+

DELIVERIES таблица:

    +-------------+------------+------------+
    | DELIVERY_ID | COURIER_ID | START_DATE |
    +-------------+------------+------------+

ORDERS таблица:

    +----------+-------------+-------------+
    | ORDER_ID | DELIVERY_ID | FINISH_DATE |
    +----------+-------------+-------------+

COORDINATES таблица:

    +-------------+-----+-----+------+
    | DELIVERY_ID | LAT | LNG | DATE |
    +-------------+-----+-----+------+

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

Что мне нужно?

  • SQL-запрос, который возвращает всех курьеров [COURIER_ID], их последнюю доставку [DELIVERY_ID] (на основе последних START_DATE), последние координаты доставки [LAT и LNG] (на основе последних DATE) и количество оставшихся заказов (общее количество заказов последняя поставка, в которой нет FINISH_DATE).

  • У курьера не может быть доставок, в этом случае я хочу, чтобы в результате DELIVERY_ID = NULL, LAT = NULL и LNG = NULL.

  • У доставки может не быть координат, в этом случае я хочу, чтобы в результате было LAT = NULL и LNG = NULL.

Что я смог сделать?

SELECT c.`COURIER_ID`,
       d.`DELIVERY_ID`,
       r.`LAT`,
       r.`LNG`,
  (SELECT COUNT(DISTINCT `ORDER_ID`)
   FROM `ORDERS`
   WHERE `DELIVERY_ID` = d.`DELIVERY_ID`
     AND `FINISH_DATE` IS NULL) AS REMAINING_ORDERS

FROM `COURIERS` AS c
LEFT JOIN `DELIVERIES` AS d USING (`COURIER_ID`)
LEFT JOIN `COORDINATES` AS r ON r.`DELIVERY_ID` = d.`DELIVERY_ID`

WHERE (CASE WHEN
         (SELECT MAX(`START_DATE`)
          FROM `DELIVERIES`
          WHERE `COURIER_ID` = c.`COURIER_ID`) IS NULL THEN d.`START_DATE` IS NULL ELSE d.`START_DATE` =
         (SELECT MAX(`START_DATE`)
          FROM `DELIVERIES`
          WHERE `COURIER_ID` = c.`COURIER_ID`) END)
  AND (CASE WHEN
         (SELECT MAX(`DATE`)
          FROM `COORDINATES`
          WHERE `DELIVERY_ID` = d.`DELIVERY_ID`) IS NULL THEN r.`DATE` IS NULL ELSE r.`DATE` =
         (SELECT MAX(`DATE`)
          FROM `COORDINATES`
          WHERE `DELIVERY_ID` = d.`DELIVERY_ID`) END)
GROUP BY c.`COURIER_ID`
ORDER BY d.`START_DATE` DESC

Проблема в том, что этот запрос очень медленный (от 5 до 20 секунд), когда у меня более 5к COORDINATES и иногда не возвращает всех курьеров.

Большое спасибо за любое решение.


mysql sql group-by select left-join
person Lucas NN    schedule 05.09.2014    source источник
comment
Вы толком не задали вопрос. Похоже, вы ищете помощь в оптимизации запроса, который вы уже написали. Вам следует уточнить свой вопрос. Если вам нужна помощь в оптимизации запроса, вам следует указать желаемые результаты.   -  person axiopisty    schedule 05.09.2014
comment
Я считаю, что это вопрос, потому что этот запрос не работает так, как мне нужно, независимо от его производительности. Производительность — лишь одна из проблем. Спасибо.   -  person Lucas NN    schedule 05.09.2014
comment
Должны ли вы возвращать все результаты в одном запросе к базе данных? Или вы можете сделать пару запросов к базе данных, если конечным результатом будет правильный набор данных?   -  person axiopisty    schedule 05.09.2014
comment
Было бы также полезно, если бы вы включили образцы данных в свои таблицы вместе с ожидаемыми результатами.   -  person axiopisty    schedule 05.09.2014
comment
Использование max(date) может получить несколько строк вместо последней.   -  person Jaugar Chang    schedule 05.09.2014
comment
@axiopisty Для меня лучший способ — использовать только один запрос, но если вы можете придумать хорошее решение, используя более одного запроса, я могу изменить свой код.   -  person Lucas NN    schedule 05.09.2014
comment
@axiopisty Я попытаюсь получить образцы данных. Спасибо.   -  person Lucas NN    schedule 05.09.2014
comment
@JaugarChang О, хороший глаз, я этого не осознавал.   -  person Lucas NN    schedule 05.09.2014
comment
Сначала вы можете сгенерировать два подзапроса delivery's last coordinates и courier's last deliveries. Чем соединить их вместе. Он должен быть более эффективным. Существует решение получения первой или последней строки в определенной группе с помощью MySql, на которое вы можете ссылаться   -  person Jaugar Chang    schedule 05.09.2014
comment
@axiopisty Почему ты удалил свой ответ? Я хотел бы попробовать оба ответа и выбрать лучший. знак равно   -  person Lucas NN    schedule 05.09.2014
comment
Я удалил его, потому что он был неправильным. Я забыл включить количество оставшихся заказов. Но теперь я исправил ответ и разместил его повторно. Это все еще может быть неправильно из-за моей интерпретации вашего вопроса. Неясно, является ли рассматриваемая final_date датой в таблице заказов или таблице поставок.   -  person axiopisty    schedule 05.09.2014
comment
@axiopisty Спасибо. В этом случае FINISH_DATE в таблице DELIVERIES бесполезна. Я отредактирую вопрос.   -  person Lucas NN    schedule 05.09.2014

Ответы (2)


arrow_upward
1
arrow_downward

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

select
  c.courier_id
  , d.delivery_id
  , co.lat
  , co.lng
  , oc.cnt as remaining_orders
from
  couriers c
  left join (
    select
      d.delivery_id
      , d.courier_id
    from
      deliveries d
      inner join (
        select
          d.delivery_id
          , max(d.start_date) as start_date
        from
          deliveries d
        group by
          d.delivery_id
      ) dmax on dmax.delivery_id = d.delivery_id and dmax.start_date = d.start_date
  ) d on d.courier_id = c.courier_id
  left join (
    select
      c.delivery_id
      , c.lat
      , c.lng
    from
      coordinates c
      inner join (
        select
          c.delivery_id
          , max(c.date) as date
        from
          coordinates c
        group by
          c.delivery_id
      ) cmax on cmax.delivery_id = c.delivery_id and cmax.date = c.date
  ) co on co.delivery_id = d.delivery_id
  left join (
    select
      o.delivery_id
      , count(o.order_id) as cnt
    from
      orders o
    where
      o.finish_date is null
    group by
      o.delivery_id
  ) oc on oc.delivery_id = d.delivery_id
person axiopisty    schedule 05.09.2014
comment
Спасибо, я попробую ваш ответ и вернусь, чтобы сказать, сработало ли это. - person Lucas NN; 05.09.2014

arrow_upward
2
arrow_downward

Попробуй это:

SELECT C.COURIER_ID, D.DELIVERY_ID, D.START_DATE, D.FINISH_DATE, 
       B.LAT, B.LNG, B.DATE, C.NoOfOrders
FROM COURIERS C 
LEFT JOIN ( SELECT * 
            FROM (SELECT * 
                  FROM DELIVERIES D 
                  ORDER BY D.COURIER_ID, D.START_DATE DESC
                ) A 
            GROUP BY COURIER_ID
          ) AS A ON C.COURIER_ID = A.COURIER_ID 
LEFT JOIN ( SELECT * 
            FROM (SELECT * 
                  FROM COORDINATES CO 
                  ORDER BY CO.DELIVERY_ID, CO.DATE DESC
                ) B
            GROUP BY CO.DELIVERY_ID
          ) AS B ON A.DELIVERY_ID = B.DELIVERY_ID 
LEFT JOIN ( SELECT O.DELIVERY_ID, COUNT(1) NoOfOrders
            FROM ORDERS O WHERE FINISH_DATE IS NULL
            GROUP BY O.DELIVERY_ID
          ) AS C ON A.DELIVERY_ID = C.DELIVERY_ID;
person Saharsh Shah    schedule 05.09.2014
comment
Спасибо, я попробую ваш ответ и вернусь, чтобы сказать, сработало ли это. - person Lucas NN; 05.09.2014