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

Shopen · 11.06.2013 19:28:43

Что то голова совсем не поймет с какой стороны...

Есть три таблицы - договора (orders), счета (invoices) и продукция (products). В договорах пусть один столбец - id_order. В счетах два - id_invoice и id_order (id договора к которому привязан счет)
Продукция выглядит так:

CREATE TABLE `products` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `type_document` varchar(20) NOT NULL COMMENT 'Тип документа, либо order либо invoice',

 `product_type_id` int(11) unsigned NOT NULL COMMENT 'Тип продукции',

 `product_count` int(9) NOT NULL COMMENT 'Количество',

 `product_price` decimal(11,2) NOT NULL COMMENT 'Стоимость',

 `id_document` int(11) unsigned NOT NULL COMMENT 'ID документа',

 PRIMARY KEY (`id`),

 KEY `id_document` (`id_document`,`product_type_id`,`type_document`)

)

В этой таблице хранится перечень продукции привязанной к документу по id_document + type_document (напр. 5-order, т.е. договор с id=5). Справочник продукции лежит отдельно, для решения задачи не важен

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

Any ideas? )

Заранее спасибо

vasya · 11.06.2013 19:50:36

select `id_document` from `products` where `type_document`='order' group by `id_document` having sum(`product_price`) > X 

union 

(select o.id_order from orders o join invoices using(id_order) group by id_order having count(id_invoice) > N);

??
Нет под рукой базы, чтобы проверить.

Shopen · 11.06.2013 20:05:40

Не... нету никаких X и N.
Допустим пример

Договора - id_order = 5 и 6
Привязанные к ним счета - id_invoice = 10 и 11
соответственно

В таблице продукции следующее

id    id_document    type_document    product_type_id    product_count    product_price

   5    order    1    105    20000    

   5    order    2    50    3000

   5    order    3    40    2400

   6    order    1    20    2000    

   6    order    2    100    6000

   6    order    3    35    6000

   10    invoice    1    105    20000    

   10    invoice    2    200    12000

   10    invoice    3    40    2400

   11    invoice    1    20    2000

   11    invoice    2    100    6000

   11    invoice    3    35    6000

Отсюда видно что пара 6-11 правильная (количество продукции по каждой позиции в счетах не превышает соответствующих позиции для договора), а пара 5-10 - не правильная (в счете превышено количество и сумма по продукции product_type_id = 2). Соответственно нужный запрос должен вернуть таблицу с одной строкой - id_order = 5.
Ну и на самом деле всё чуть еще сложнее так как счетов к договору может быть привязано несколько, по которым раскидана продукция (в данном примере по одному счету на договор)

Отредактированно Shopen (11.06.2013 22:57:52)

vasya · 12.06.2013 00:33:41

Для простого случая (один счет на договор):

select t1.id_document from

(select * from products where type_document='order') t1 join invoices i on t1.id_document=i.id_order

join

(select * from products where type_document='invoice') t2 on t2.id_document=i.id_invoice

where t1.product_type_id=t2.product_type_id and (t1.product_count<>t2.product_count or t1.product_price<>t2.product_price);

vasya · 12.06.2013 00:48:05

Для общего

select t1.product_type_id, t1.product_count, t1.product_price, t1.id_document, sum(t2.product_count) x, sum(t2.product_price) y from

(select * from products where type_document='order') t1 join invoices i on t1.id_document=i.id_order

join

(select * from products where type_document='invoice') t2 on t2.id_document=i.id_invoice

where t1.product_type_id=t2.product_type_id

group by t1.id_document, t1.product_type_id

having t1.product_count <> x or t1.product_price <> y;

суммирование можно перенести в часть having, оставив в перечислении только id_document. Я привел в таком виде, чтобы было наглядней.

Shopen · 13.06.2013 00:55:57

Ох, спасибо )
Выйду на работу буду осмыслять и тестировать.

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

#1 11.06.2013 19:28:43

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

#2 11.06.2013 19:50:36

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

#3 11.06.2013 20:05:40

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

#4 12.06.2013 00:33:41

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

#5 12.06.2013 00:48:05

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

#6 13.06.2013 00:55:57

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

Board footer