Помогите с SQL-запросом

← →
ProgRAMmer Dimonych © (2012-10-18 13:57) [0]

Есть три таблицы:

- Products (prID, prDisplayName, ...)
- Operations (opID, opDate, ...)
- OperationItems (oifkOperation, oifkProduct, oiAmount, oiPrice)

Речь идёт о продуктовом складе. В Products перечисляются все продукты, с которыми работает склад. Каждой строке в Operations соответствует приезд грузовика с продуктами. Что именно привёз грузовик - это строки OperationItems с соответствующим oifkOperation.

"Для замыслов каких-то непонятных" по этим трём таблицам нужно делать выборку информации по каждому из продуктов. При этом хлеб за 100 рублей и хлеб за 150 рублей должны учитываться отдельно. Информация по каждому "продукт A по цене B" нужна следующая:

- общее количество продукта на складе;
- количество продукта за вчерашний день;
- величина последнего поступления продукта (за один день может быть несколько поступлений);
- и т.п.

Вся информация должна в итоге стать одним резалт-сетом.

Для первых двух пунктов м.б. через "как обычно", но добился выборки:

SELECT `stoOperationItems`.`oifkProduct` AS prID, SUM(`stoOperationItems`.`oiAmount`) AS prLeft, SUM(IF(`stoOperations`.`opDateTime` = DATE_SUB(UTC_DATE(), INTERVAL 1 DAY), `stoOperationItems`.`oiAmount`, 0)) AS prYesterday, `stoProducts`.`prDisplayName` AS prDisplayName FROM `stoOperationItems` INNER JOIN `stoProducts` ON `stoProducts`.`prID` = `stoOperationItems`.`oifkProduct` INNER JOIN `stoOperations` ON `stoOperations`.`opID` = `stoOperationItems`.`oifkOperation` GROUP BY `stoOperationItems`.`oifkProduct`, `stoOperationItems`.`oiPrice`;

Проблема в выборке последнего поступления товара: как вкрутить это в этот же запрос?

P.S. Рекомендации по оптимизации запроса также приветствуются.

← →
Компромисс © (2012-10-18 14:01) [1]

Никак. Если надо по каждому продукту, то никакой группировки быть не должно.

← →
Игорь Шевченко © (2012-10-18 14:01) [2]

СУБД конечно же DB2 ?

← →
ProgRAMmer Dimonych © (2012-10-18 14:06) [3]

> [2] Игорь Шевченко © (18.10.12 14:01)

Упс, собирался же написать.

MySQL

← →
Компромисс © (2012-10-18 14:07) [4]

Нет, вру. Можно.
Надо вытаскивать amount from items where price= and product= and operationId = (select operationId where operDate = (select max(operDate) from operation where price= and product=))

← →
ProgRAMmer Dimonych © (2012-10-18 14:16) [5]

> [4] Компромисс © (18.10.12 14:07)

Самый вложенный подзапрос: поля price и product - они в items, а не в operations. Стало быть ещё JOINить придётся? Или можно в обход?

← →
Компромисс © (2012-10-18 14:46) [6]

ProgRAMmer Dimonych © (18.10.12 14:16) [5]

Да, получается придется joinить.

← →
Компромисс © (2012-10-18 14:49) [7]

Можно еще попробовать через
from operations A
where not exists (select 1 from operations MO join items on ... where product= and price= and MO.operDate > A.operDate)

← →
ProgRAMmer Dimonych © (2012-10-18 15:04) [8]

Пока изобразил вот таким куском:

SELECT ..., ( SELECT `oiAmount` FROM `stoOperationItems` WHERE `oifkProduct` = prID AND `oiPrice` = prPrice AND `oifkOperation` = ( SELECT `opID` FROM `stoOperations` INNER JOIN `stoOperationItems` ON `stoOperationItems`.`oifkOperation` = `stoOperations`.`opID` WHERE `stoOperationItems`.`oifkProduct` = prID AND `stoOperationItems`.`oiPrice` = prPrice ORDER BY `stoOperations`.`opDateTime` DESC, `stoOperations`.`opID` DESC LIMIT 1 ) LIMIT 1 ) AS prLast, ...

Вроде работает, но замечания и предложения по-прежнему принимаются.

← →
Компромисс © (2012-10-18 15:09) [9]

Если СУБД такое поддерживает, то один вложенный select лишний. Можно сразу брать amount, а сортировать по operDate

← →
ProgRAMmer Dimonych © (2012-10-18 15:47) [10]

> [9] Компромисс © (18.10.12 15:09)

Да, действительно. Спасибо за подсказку. Упростил:

SELECT `stoOperationItems`.`oifkProduct` AS prID, `stoOperationItems`.`oiPrice` AS prPrice, SUM(`stoOperationItems`.`oiAmount`) AS prLeft, SUM(IF(`stoOperations`.`opDateTime` = DATE_SUB(UTC_DATE(), INTERVAL 1 DAY) AND `stoOperations`.`opType` = 1, -`stoOperationItems`.`oiAmount`, 0)) AS prYesterday, ( SELECT `oiAmount` FROM `stoOperationItems` INNER JOIN `stoOperations` ON `stoOperationItems`.`oifkOperation` = `stoOperations`.`opID` WHERE `oifkProduct` = prID AND `oiPrice` = prPrice ORDER BY `stoOperations`.`opDateTime` DESC, `stoOperations`.`opID` DESC LIMIT 1 ) AS prLast, `stoProducts`.`prDisplayName` AS prDisplayName FROM `stoOperationItems` INNER JOIN `stoProducts` ON `stoProducts`.`prID` = `stoOperationItems`.`oifkProduct` INNER JOIN `stoOperations` ON `stoOperations`.`opID` = `stoOperationItems`.`oifkOperation` GROUP BY `stoOperationItems`.`oifkProduct`, `stoOperationItems`.`oiPrice`;

Ещё один вопрос: в подзапросе есть INNER JOIN с stoOperations. Попробовал его закомментировать - вроде всё отработало корректно. Можно ли безопасно убрать этот JOIN, опираясь на то, что он есть в основном запросе? Или мне просто повезло с тестовыми данными таблицы?

← →
Компромисс © (2012-10-18 15:56) [11]

> Или мне просто повезло с тестовыми данными таблицы?

Просто повезло.

Я бы переместил Operations из основного запроса в запрос по yesterday.
Точнее, даже не так. У меня бы внеший запрос состоял только из Product. Группировка нужна только для yesterday

Хотя и для суммы тоже...

> Я бы переместил Operations из основного запроса в запрос
> по yesterday.

а я бы убрал этот оба подзапроса

подзапрос перенести во FROM

вот это:

(
SELECT `oiAmount`
FROM `stoOperationItems`
INNER JOIN `stoOperations` ON `stoOperationItems`.`oifkOperation` = `stoOperations`.`opID`
WHERE `oifkProduct` = prID
AND `oiPrice` = prPrice
ORDER BY `stoOperations`.`opDateTime` DESC, `stoOperations`.`opID` DESC
LIMIT 1
) AS prLast,

сделать производной таблицей:
(
SELECT `oiAmount`
FROM `stoOperationItems`
where даты_поступления IN (выбрать максимальные даты сгруппированные по товарам, ценам)
) zzz
и выбирать max(zzz.oiAmount)

Помогите с SQL-запросом Найти похожие ветки