SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 24.03.2018 23:17:28

Valdimar
Участник
Зарегистрирован: 24.03.2018
Сообщений: 4

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

Здравствуйте!
Есть три таблицы order,order_line, product,
нужно обратится с прямым запросом что бы получилось как на фото

Где Name - имя покупателя, Result - два наибольших заказа через запятую, слева id заказа и справа - сумма по заказу, колонка TotalSumPerCustomer - показывает общую сумму покупок клиента.

У меня проблемы со средним столбцом, так что бы оставить два самых больших сапроса(не более двух)

SELECT o.customer_name, GROUP_CONCAT(CONCAT_WS(' - ', ol.order_id, p.price) ORDER BY p.price DESC SEPARATOR ' , ')
FROM .order o
LEFT JOIN order_line ol
ON o.id = ol.order_id
LEFT JOIN product p
ON ol.product_id = p.id
GROUP BY o.customer_name






c таким дампом:

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEAFULT '',
  `price` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `product` (`id`, `name`, `price`) VALUES
  (1, 'iPhone', 101.99),
  (2, 'Samsung Galaxy', 92.99),
  (3, 'Lenovo P2', 88.79),
  (4, 'HP Pavilion', 98.79);


CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `order` (`id`, `customer_name`) VALUES
  (1, 'Bob'),
  (2, 'Jhon'),
  (3, 'Jane'),
  (4, 'Anna'),
  (5, 'Linda'),
  (6, 'Jane'),
  (7, 'Anna'),
  (8, 'Jane'),
  (9, 'Bob'),
  (10, 'Bob'),
  (11, 'Linda'),
  (12, 'Linda');

CREATE TABLE `order_line` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(7) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `order`(`id`),
  FOREIGN KEY (`product_id`) REFERENCES `product`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `order_line` (`id`, `order_id`, `product_id`, `quantity`) VALUES
  (1, 1, 1, 2),
  (2, 1, 3, 3),
  (3, 1, 4, 1),
  (4, 2, 2, 10),
  (5, 2, 3, 5),
  (6, 3, 1, 2),
  (7, 4, 2, 1),
  (8, 5, 4, 6),
  (9, 6, 1, 7),
  (10, 7, 1, 8),
  (11, 7, 4, 5),
  (12, 8, 1, 3),
  (13, 9, 3, 7),
  (14, 10, 2, 10),
  (15, 11, 1, 4),
  (16, 11, 2, 3),
  (17, 12, 1, 7),
  (18, 12, 3, 10),
  (19, 12, 4, 4);
 

Отредактированно Valdimar (25.03.2018 02:32:32)

Неактивен

 

#2 25.03.2018 02:38:48

Valdimar
Участник
Зарегистрирован: 24.03.2018
Сообщений: 4

Re: Помогите с подзапросом

Name          Result                                 TotalSumPerCustumer
1    Linda         12 -1996.99, 11 - 686.93            3276.66
2    Bob           10 -929.90, 9 - 621.53                2120.57
3    Anna          7 - 1309.87, 4 - 92.99               1402.86
4    John          2 - 1373.85                               1373.85
5    Jane          6 - 717.93, 8 - 305.97                1223.88

Фото не загрузилось

Неактивен

 

#3 25.03.2018 03:43:32

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с подзапросом

select customer_name,
(select concat(t1.order_id, ' - ', t1.order_sum, ',', ifnull(t2.order_id,''), ' - ', ifnull(t2.order_sum,'')) from
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t1
left join
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t2
on t1.customer_name=t2.customer_name and t1.order_sum>t2.order_sum
where t1.customer_name = t3.customer_name
order by t1.customer_name, t1.order_sum desc, t2.order_sum desc limit 1) col2,
sum(order_sum) total_sum_client from
(select customer_name, t.* from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t3
group by customer_name

результат совпадает:
'Anna', '7 - 1309.87,4 - 92.99', 1402.86
'Bob', '10 - 929.90,9 - 621.53', 2120.57
'Jane', '6 - 713.93,8 - 305.97', 1223.88
'Jhon', '2 - 1373.85, - ', 1373.85
'Linda', '12 - 1996.99,11 - 686.93', 3276.66

Неактивен

 

#4 25.03.2018 03:51:42

Valdimar
Участник
Зарегистрирован: 24.03.2018
Сообщений: 4

Re: Помогите с подзапросом

какой же он огромен :mad
Vasya ты спас только три дня жизни моей, +100500 в твою карму!

....как часто в твоей жизни на работе приходится работать с такими запросами?

Неактивен

 

#5 25.03.2018 04:04:18

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с подзапросом

Valdimar написал:

какой же он огромен :mad

давайте ещё немного увеличим smile
чтобы правильно обрабатывался случай, когда у пользователя два заказа с одинаковой максимальной стоимостью

select customer_name,
(select concat(t1.order_id, ' - ', t1.order_sum, ',', ifnull(t2.order_id,''), ' - ', ifnull(t2.order_sum,'')) from
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t1
left join
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t2
on t1.customer_name=t2.customer_name and t1.order_sum>=t2.order_sum and t1.order_id <> t2.order_id
where t1.customer_name = t3.customer_name
order by t1.customer_name, t1.order_sum desc, t2.order_sum desc limit 1) col2,
sum(order_sum) total_sum_client from
(select customer_name, t.* from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t3
group by customer_name

Неактивен

 

#6 25.03.2018 04:08:37

Valdimar
Участник
Зарегистрирован: 24.03.2018
Сообщений: 4

Re: Помогите с подзапросом

vasya написал:

Valdimar написал:

какой же он огромен :mad

давайте ещё немного увеличим smile
чтобы правильно обрабатывался случай, когда у пользователя два заказа с одинаковой максимальной стоимостью

select customer_name,
(select concat(t1.order_id, ' - ', t1.order_sum, ',', ifnull(t2.order_id,''), ' - ', ifnull(t2.order_sum,'')) from
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t1
left join
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t2
on t1.customer_name=t2.customer_name and t1.order_sum>=t2.order_sum and t1.order_id <> t2.order_id
where t1.customer_name = t3.customer_name
order by t1.customer_name, t1.order_sum desc, t2.order_sum desc limit 1) col2,
sum(order_sum) total_sum_client from
(select customer_name, t.* from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t3
group by customer_name

Вот с изменёнными названиями как в таблице и....
select customer_name AS Name,
(select concat(t1.order_id, ' - ', t1.order_sum, ',', ifnull(t2.order_id,''), ' - ', ifnull(t2.order_sum,'')) from
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t1
left join
(select customer_name, order_id, order_sum from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t2
on t1.customer_name=t2.customer_name and t1.order_sum>=t2.order_sum and t1.order_id <> t2.order_id
where t1.customer_name = t3.customer_name
order by t1.customer_name, t1.order_sum desc, t2.order_sum desc limit 1) Result,
sum(order_sum) TotalSumPerSecond from
(select customer_name, t.* from `order` join
(select `order_id`, sum(`quantity` * `price`) order_sum
from `order_line` join `product` on `product`.id = `order_line`.product_id
group by `order_id`) t on `order`.id = t.order_id) t3
group by customer_name



.....сейчас отсортирую по Desc

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson