Блог

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

Запросы на покупку связаны с местом отгрузки (складом).

У меня есть таблица :

 CREATE TABLE IF NOT EXISTS `shipment_locations` (
  `id` int(6) primary key,
  `name` varchar(200) NOT NULL
);
INSERT INTO `shipment_locations` (`id`, `name`) VALUES
  ('1', 'france'),
  ('2', 'usa'),
  ('3', 'spain'),
  ('4', 'germany');
  
CREATE TABLE IF NOT EXISTS `purchase_requests` (
  `id` int(6) primary key,
  `name` varchar(200) NOT NULL,
  `total_cost_confirmed` int(6) NULL,
  `shipment_location_id` int(6) NULL,
 FOREIGN KEY (`shipment_location_id`) REFERENCES `shipment_locations` (`id`)
);

INSERT INTO `purchase_requests` (`id`, `name`, `total_cost_confirmed`, `shipment_location_id`) VALUES
  ('1', 'pr1', '109', 1),
  ('2', 'pr2', '1500', 3),
  ('3', 'pr3', '3000', 2),
  ('4', 'pr4', '10000', 2),
  ('5', 'pr5', '5', 3),
  ('6', 'pr6', '3000', 2),
  ('7', 'pr7', '3000', 2),
  ('8', 'pr8', '1', 3),
  ('9', 'pr9', '10000000', 3);
 

Для заказа по местоположению отгрузки с наиболее подтвержденной стоимостью это довольно просто :

 SELECT shipment_location_id, SUM(total_cost_confirmed) totalConfirmed
FROM purchase_requests
GROUP BY shipment_location_id
ORDER BY totalConfirmed DESC
 

Здесь это работает отлично: http://sqlfiddle.com /#!9/732f32/2/0

Но затем я попытался отфильтровать по идентификатору запроса на покупку (добавив GROUP BY id и WHERE id IN(...) ), это дает мне неправильный порядок (потому что он принимает идентификаторы, присутствующие в результате). => (sqlfiddle)

Как я могу сохранить правильный порядок из первого запроса при фильтрации по идентификатору запроса на покупку?

Добавление sqlfiddle: Sqlfiddle

Заранее спасибо за вашу помощь 🙂

Сначала агрегируйте, чтобы получить сумму totalConfirmed , а затем присоединитесь к таблице:

 SELECT p.id, p.name, p.shipment_location_id, t.totalConfirmed
FROM purchase_requests p 
INNER JOIN (
  SELECT shipment_location_id, SUM(total_cost_confirmed) totalConfirmed 
  FROM purchase_requests
  GROUP BY shipment_location_id
) t ON t.shipment_location_id = p.shipment_location_id
WHERE p.id IN ('1', '3', '4', '8')
ORDER BY t.totalConfirmed DESC
 

Смотрите демонстрацию.
Результаты:

 > id | name | shipment_location_id | totalConfirmed
> -: | :--- | -------------------: | -------------:
>  4 | pr4  |                    2 |          19000
>  3 | pr3  |                    2 |          19000
>  8 | pr8  |                    1 |            110
>  1 | pr1  |                    1 |            110