Меню
Что нового?

Сложный запрос

Panchous

Павел
Сложный запрос

Таблица продуктов: product
[sql]
CREATE TABLE `product` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(10) unsigned NOT NULL default '0',
`title` varchar(50) NOT NULL default '',
`description` tinytext,
`price` decimal(5,2) unsigned NOT NULL default '0.00',
`presence` tinyint(1) NOT NULL default '0',
`image` varchar(20) NOT NULL default '',
`leader_image` varchar(20) default NULL,
`leader_order` tinyint(1) default NULL,
`hot_image` varchar(20) default NULL,
`hot_order` tinyint(1) default NULL,
PRIMARY KEY (`product_id`),
KEY `category_id` (`category_id`),
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `catalog` (`category_id`) ON DELETE CASCADE ON UPDA
TE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
[/sql]
Таблица ключей продуктов: product_key
[sql]CREATE TABLE `product_key` (
`file_id` int(10) unsigned NOT NULL auto_increment,
`product_id` int(10) unsigned default NULL,
`order_id` int(10) unsigned default NULL,
`filename` varchar(20) NOT NULL default '',
PRIMARY KEY (`file_id`),
KEY `product_id` (`product_id`),
KEY `order_id` (`order_id`),
CONSTRAINT `product_key_ibfk_5` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`),
CONSTRAINT `product_key_ibfk_6` FOREIGN KEY (`order_id`) REFERENCES `user_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
[/sql]
Таблица заказов: user_order
[sql]CREATE TABLE `user_order` (
`order_id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL default '0',
`service_id` tinyint(3) unsigned NOT NULL default '0',
`reg_date` datetime NOT NULL default '0000-00-00 00:00:00',
`payment_date` datetime default NULL,
`perform_date` datetime default NULL,
`sum` decimal(5,2) default NULL,
PRIMARY KEY (`order_id`),
KEY `user_id` (`user_id`),
KEY `service_id` (`service_id`),
CONSTRAINT `user_order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `user_order_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `pay_service` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
[/sql]

ВОПРОС:
Можно ли одним запросом сделать следующую выборку...
Выбрать для всех продуктов:
наименование, кол-во свободных ключей (у которых order_id=NULL) и кол-во проданных ключей за определенный период (смотрится по полям связанного заказа)?

Версия MySQL: 4.1.
Спасибо.

-~{}~ 22.02.06 12:17:

[sql]select p.title, count(k_sell.product_id) AS sell_count, count(k_free.product_id) AS free_count
from product p
left outer join product_key k_free on (p.product_id=k_free.product_id and k_free.order_id is null)
left outer join product_key k_sell
on (p.product_id=k_sell.product_id and k_sell.order_id in
(select o.order_id from user_order o where DATE(o.perform_date) between '2005-01-01' and '2006-02-21')
)
group by p.title[/sql]

-~{}~ 22.02.06 12:42:

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

chira

Новичок
попробуй так:
Код: 
SELECT p.title
  , count( IF(pk.order_id IS NULL,NULL,pk.product_id) ) AS sell_count
  , count( IF(pk.order_id IS NULL,pk.product_id,NULL) ) AS free_count
FROM product p
LEFT JOIN product_key pk ON p.product_id = pk.product_id
LEFT JOIN user_order o ON pk.order_id = o.order_id AND  o.perform_date BETWEEN '2005-01-01 00:00:00' AND '2006-02-21 23:59:59'
GROUP BY p.title
 

Panchous

Павел
да, вроде пашет...
если несложно. поясни изменения мне на будущее

-~{}~ 22.02.06 14:33:

не работает!
всегда выводит общее кол-во проданных и оставшихся товаров!
вне зависимости от заданных в выборке дат

-~{}~ 22.02.06 14:49:

EXPLAIN для приведенного мной запроса:
Код: 
+----+--------------------+--------+-----------------+---------------------+----------+---------+-------+------+---------------------------------+
| id | select_type        | table  | type            | possible_keys       | key      | key_len | ref   | rows | Extra                           |
+----+--------------------+--------+-----------------+---------------------+----------+---------+-------+------+---------------------------------+
|  1 | PRIMARY            | p      | ALL             | NULL                | NULL     |    NULL | NULL  |    2 | Using temporary; Using filesort |
|  1 | PRIMARY            | k_free | ref             | product_id,order_id | order_id |       5 | const |    0 |                                 |
|  1 | PRIMARY            | k_sell | ALL             | product_id          | NULL     |    NULL | NULL  |    3 |                                 |
|  2 | DEPENDENT SUBQUERY | o      | unique_subquery | PRIMARY             | PRIMARY  |       4 | func  |    1 | Using index; Using where        |
+----+--------------------+--------+-----------------+---------------------+----------+---------+-------+------+---------------------------------+
-~{}~ 22.02.06 15:04:

замечено так же, что в результирующей выборке:
sell_count = free_count = реальное_sell_count*реальное_free_count
 

chira

Новичок
возможно так:
Код: 
SELECT p.title
  , count( IF(pk.order_id IS NULL,NULL,o.order_id) ) AS sell_count
  , count( IF(pk.order_id IS NULL,pk.product_id,NULL) ) AS free_count
FROM product p
LEFT JOIN product_key pk ON p.product_id = pk.product_id
LEFT JOIN user_order o ON pk.order_id = o.order_id AND  o.perform_date BETWEEN '2005-01-01 00:00:00' AND '2006-02-21 23:59:59'
GROUP BY p.title
-~{}~ 22.02.06 14:27:

в твоём запросе смущает условие:
LEFT OUTER JOIN product_key k_free ON ( p.product_id = k_free.product_id AND k_free.order_id IS NULL )
можешь подробнее объяснить, что имелось ввиду ...
из условия видно, что в таблице могут быть записи у которых k_free.order_id IS NULL, что это за случай?
 

Panchous

Павел
1. у продукта есть ключи
2. ключи могут быть свободны (order_id==NULL) или проданы (order_id==ID заказа)

Нужно вывести все продукты и статистику по ним:
кол-во свообдных ключей,
кол-во проданных ключей за определенную дату (дата продажи указана в связанном заказе)

LEFT OUTER JOIN product_key k_free ON ( p.product_id = k_free.product_id AND k_free.order_id IS NULL )
выбирает свободные ключи для продукта

-~{}~ 22.02.06 16:08:

вторая версия твоего запроса работает :)
поясни плиз условия: что я не так делал?
 

chira

Новичок
в твоём запросе
каждый отдельный JOIN считается правильно
Нажмите для раскрытия...
в тоже время объединение таблицы product_key для двух случаев отличается только условием k_free.order_id IS NULL
я вынес это условие в COUNT(IF(pk.order_id IS NULL,pk.product_id,NULL))
count считает не NULL значения
на значение free_count, таблица user_order, не будет влиять т.к. для условия соединения pk.order_id = o.order_id не будет найдено ни одной записи

для значения sell_count считаем все не NULL o.order_id
count( IF(pk.order_id IS NULL,NULL,o.order_id) )
возможно здесь можно обойтись без IF: count(o.order_id)

я переписал твой вариант без подзапроса
 

Panchous

Павел
chira, спасибо большое тебе!
Респект!

-~{}~ 22.02.06 18:56:

так есть - для sell_count IF не нужен
 
Войдите или зарегистрируйтесь для ответа.
Сверху