SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 11.12.2017 21:19:42

tookind
Участник
Зарегистрирован: 11.12.2017
Сообщений: 3

Проблема с запросом

Здравствуйте!
Не получается создать правильный запрос . Создала 3 таблицы c данными :

1 табл order :
id| name

1 Olya
2 Nata
3 Lara
4 Jane
5 Lena
6 Lara
7 Jane
8 Lara
9 Olya
10 Olya
11 Lena
12 Lena

2 табл order_make:
id | order_id| product_id | quantity

1 1 2 4
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

3 табл product :
id| name|price
1 Sams 100.99
2 Apple 91.99
3 HTC 87.29
4 LG 97.79



Не получается написать правильный запрос что бы из этих трех таблиц вывел итоговую таблицу :


Есть 1я таблица
id| name
1 Olya
2 Nata
3 Lara
4 Jane
5 Lena
6 Lara
7 Jane
8 Lara
9 Olya
10 Olya
11 Lena
12 Lena

Покажется смешно,но наверное лучше обьяснить не смогу:
В ней 5 юзеров, (id) из 1й табицы привязан к второй таблице (order_id) .
1)Lena(id -12,11,5) сделала заказ с второй таблицы (order_id это id покупателя). В первый заход Lena id-12 купила через вторую таблицы (id -19,product_id - 4 quantity - 4)
то есть она купила 4 телефона LG по цене 97.79 = 391.16; тоже самое c (id -18 ,product_id - 3,quantity-10) = 87.29 * 10 = 872.9 ; потом(id -17 ,product_id - 1,quantity-7) = 100.99 * 7 = 706.93 ; Тоесть за первый заход Lena id 12 = 1970.99(идет в result).
За второй заход та самая Lena но с id 11(id -16 ,product_id - 2,quantity-3) 91.99 * 3 = 275.97 , пототм Lena id 11 (id -15 ,product_id - 1,quantity-4)100.99 *4 = 403.96. Lena id-11 = 679.93(идет в result);
Забыла еще в result написать Lena id-5, (id -8 ,product_id - 4,quantity-6)97.79* 6 =586.74 . это идет в столбец result тоже;
В столбец TotalPerClient идет общая сумма заказа Lena = 1970.99 +679.93 +586.74 = 3237.66

Должная получится таблица :
______________________________________________________
Name | Result | | TotalPerClient
1 Lena |12 -1970.99 ,11-679.93, 5-586.74 | 3237.66
2 Olya |10 - , 9- |
3...
4...
5...
______________________________________________________

Проблема с запросом

Отредактированно tookind (11.12.2017 21:21:03)

Неактивен

 

#2 11.12.2017 21:34:22

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

Re: Проблема с запросом

join использовать умеете?
возьмите за основу

select * from `order` o join order_make on o.id=order_id join product p on product_id=p.id

вместо * оставьте нужные поля и добавляйте группировку по `order`.name

Неактивен

 

#3 11.12.2017 22:00:50

tookind
Участник
Зарегистрирован: 11.12.2017
Сообщений: 3

Re: Проблема с запросом

vasya написал:

join использовать умеете?
возьмите за основу

select * from `order` o join order_make on o.id=order_id join product p on product_id=p.id

вместо * оставьте нужные поля и добавляйте группировку по `order`.name

Через left join пыталась .целый день читаю.но я что не то пишу .У меня ерунда получается. Result  выводит без подсчета суммы; TotalPerClient тоже не получается.
Таблицы вроде правильно сделала,не понимаю в чем дело;а вот вытянуть из этих трех то что нужно не получается;
Мой дамп:


CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `price` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `product` (`id`, `name`, `price`) VALUES
  (1, 'Sams, 100.99),
  (2, '
Apple', 91.99),
  (3, '
HTC', 87.29),
  (4, '
LG', 97.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, '
Olya'),
  (2, '
Nata'),
  (3, '
Lara'),
  (4, '
Jane'),
  (5, '
Lena'),
  (6, '
Lara'),
  (7, '
Jane'),
  (8, '
Lara'),
  (9, '
Olya'),
  (10, '
Olya'),
  (11, '
Lena'),
  (12, '
Lena');
 
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);

Отредактированно tookind (11.12.2017 22:01:38)

Неактивен

 

#4 11.12.2017 22:04:46

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

Re: Проблема с запросом

про join подробней см https://sqlinfo.ru/forum/viewtopic.php?id=3210

показывайте ваш вариант как выводите result

Неактивен

 

#5 11.12.2017 22:59:41

tookind
Участник
Зарегистрирован: 11.12.2017
Сообщений: 3

Re: Проблема с запросом

vasya написал:

про join подробней см https://sqlinfo.ru/forum/viewtopic.php?id=3210

показывайте ваш вариант как выводите result

SELECT t.customer_name, GROUP_CONCAT(',', t.order_id) orders, GROUP_CONCAT(',', t.amount) amounts, SUM(t.amount) total
FROM (
    SELECT o.customer_name, ol.order_id, SUM(ol.quantity * p.price) amount
    FROM `order` o LEFT JOIN order_make ol ON ol.order_id = o.id
    LEFT JOIN product p ON p.id = ol.product_id
    GROUP BY ol.order_id, o.customer_name
    ORDER BY o.customer_name, ol.order_id
) t
GROUP BY t.customer_name 
ORDER BY `total`  DESC

не получается сделать в три столбика
+---------------+--------------------------------------+---------+
| customer_name | orders& amounts | total |
+---------------+--------------------------------------+---------+
|
|
| Lena | ,5 -586.74 ,11 -679.93,12 -1970.99 | 3237.66 |
|
|
+---------------+------------+--------------------------+---------+

Неактивен

 

#6 11.12.2017 23:16:50

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

Re: Проблема с запросом

SELECT t.customer_name, GROUP_CONCAT(concat(t.order_id, ' - ', t.amount)) orders, SUM(t.amount) total
FROM (
    SELECT o.customer_name, ol.order_id, SUM(ol.quantity * p.price) amount
    FROM `order` o LEFT JOIN order_line ol ON ol.order_id = o.id
    LEFT JOIN product p ON p.id = ol.product_id
    GROUP BY ol.order_id, o.customer_name
    ) t
GROUP BY t.customer_name
ORDER BY `total`  DESC

Неактивен

 

#7 11.12.2017 23:20:55

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

Re: Проблема с запросом

в дополнение:
вместо left join достаточно join
сортировка во from-подзапросе игнорируется
зачем в `order_line` коонка id? комбинация ( `order_id`, `product_id`) ведь уникальна?

Неактивен

 

Board footer

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