| |
|
|
| | Есть таблица:
CREATE TABLE users_orders(
id_order INT(11) NOT NULL AUTO_INCREMENT,
id_user INT(11) NOT NULL,
date_order DATE DEFAULT NULL,
number CHAR(20) NOT NULL,
volume DECIMAL(7, 2) NOT NULL,
type ENUM('personal', 'organization') NOT NULL,
PRIMARY KEY (id_order),
INDEX id_user (id_user)
)
ENGINE = MYISAM
COMMENT = 'Таблица учета заказов ';
|
В этой таблице хранятся заказы и их объемы, и тип (type).
Нужно сделать запрос, который бы выдавал итоги заказов по месяцам и по типам. Должна получиться таблица из 3-х колонок:
---
Месяц заказа | Объем за месяц оп типу personal | Объем за месяц по типу organization
---
Раньше было без типа, и все было более-менее понятно. А сейчас, изобрел вот что (только сильно не ругайтесь):
$sql = "
SELECT
personal.date,
personal.volume AS p_volume,
organization.volume AS o_volume
FROM (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = $this->id_user
AND type = 'personal'
GROUP BY date
ORDER BY $this->order $this->sort
) AS personal
LEFT JOIN (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = $this->id_user
AND type = 'organization'
GROUP BY date
ORDER BY $this->order $this->sort
) AS organization USING(date)
LIMIT $this->start, $this->numrec";
|
НО в этом запросе, если например в каком то месяце нет закзов по типу personal и есть заказа по типу organization, то эта строка пропускается. Почему она пропускается я понял. А как сделать чтобы не пропускалась, я понять не могу. Как их надо соединить, чтобы выдавалось то что дано? т.е. нужно получить таблицу из трех колонок:
---
Месяц заказа | Объем за месяц оп типу personal | Объем за месяц по типу organization
--- | |
| |
|
|
| |
|
|
| |
для: tAleks
(14.01.2011 в 22:05)
| | | >НО в этом запросе, если например в каком то месяце нет закзов по типу personal и есть заказа
>по типу organization, то эта строка пропускается. Почему она пропускается я понял.
Потому что LEFT JOIN, если поменяете вложенные запросы местами - будет наоборот. Если будет обычный JOIN, то отсутствие заказов по типу personal или по типу organization будет приводить к пропуску такой строки.
PS А хотя бы несколько записей из таблицы users_orders не подкинете? Сложно составлять запрос без возможности его проверить.
PPS А зачем вы их вообще скрещиваете, создайте два отдельных запроса. Тем более у вас они все-равно выполняются в подзапросах, только плюс еще дополнительное объединение, которое значительно дешевле можно средствами PHP осуществить. | |
| |
|
|
| |
|
|
| |
для: cheops
(14.01.2011 в 22:12)
| | | >Потому что LEFT JOIN, если поменяете вложенные запросы местами - будет наоборот. Если будет обычный JOIN, то отсутствие заказов по типу personal или по типу organization будет приводить к пропуску такой строки.
Да, это я понял.
>PS А хотя бы несколько записей из таблицы users_orders не подкинете? Сложно составлять запрос без возможности его проверить.
Вот:
INSERT INTO users_orders VALUES
(22, 1134, '2010-10-11', '', 470.00, 'personal'),
(21, 1134, '2010-09-11', '', 470.00, 'personal'),
(20, 1134, '2010-08-11', '', 470.00, 'personal'),
(19, 1134, '2010-07-11', '', 470.00, 'personal'),
(18, 1134, '2010-06-11', '', 470.00, 'personal'),
(17, 1134, '2010-05-11', '', 470.00, 'personal'),
(16, 1134, '2010-04-11', '', 470.00, 'personal'),
(15, 1134, '2010-03-11', '', 470.00, 'personal'),
(13, 1134, '2010-01-01', '', 470.00, 'personal'),
(14, 1134, '2010-02-11', '', 470.00, 'personal'),
(23, 1134, '2010-11-11', '', 470.00, 'personal'),
(24, 1134, '2010-12-11', '', 470.00, 'organization'),
(26, 1134, '2011-01-11', '', 470.00, 'personal'),
(27, 1134, '2011-01-11', '', 30.00, 'personal'),
(28, 1134, '2011-01-11', '', 150.00, 'organization'),
(29, 1134, '2011-01-14', '', 150.00, 'personal'),
(30, 1134, '2011-01-14', '', 150.00, 'organization');
|
>PPS А зачем вы их вообще скрещиваете, создайте два отдельных запроса. Тем более у вас они все-равно выполняются в подзапросах, только плюс еще дополнительное объединение, которое значительно дешевле можно средствами PHP осуществить.
При всем этом деле, нужна еще постраничная навигация. | |
| |
|
|
| |
|
|
| |
для: tAleks
(14.01.2011 в 23:04)
| | | С учетом поправок Trianon-а получилось так
SELECT
monthes.date,
personal.volume AS p_volume,
organization.volume AS o_volume
FROM
(
SELECT DISTINCT DATE_FORMAT(date_order, '%Y-%m') AS date
FROM users_orders
WHERE id_user = 1134
) AS monthes
LEFT JOIN (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = 1134
AND type = 'personal'
GROUP BY date
) AS personal USING(date)
LEFT JOIN (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = 1134
AND type = 'organization'
GROUP BY date
) AS organization USING(date)
GROUP BY monthes.date
ORDER BY monthes.date
|
PS Внутри подзапросов ORDER BY не нужно, достаточно одного, который будет сортировать результирующую таблицу. | |
| |
|
|
| |
|
|
| |
для: cheops
(14.01.2011 в 23:18)
| | | Идею понял, Спасибо!
Но не понятно вот что. Такой запрос:
SELECT
personal.date,
personal.volume AS p_volume,
organization.volume AS o_volume
FROM
(
SELECT DISTINCT DATE_FORMAT(date_order, '%Y-%m') AS date
FROM users_orders
WHERE id_user = 1134
) AS months
LEFT JOIN
(
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = 1134
AND type = 'personal'
GROUP BY date
) AS personal USING(date)
LEFT JOIN
(
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = 1134
AND type = 'organization'
GROUP BY date
) AS organization ON months.date = organization.date
GROUP BY personal.date
ORDER BY personal.date DESC
LIMIT 0, 15
|
Выдает вот такой результат:
date p_volume o_volume
2011-01 650.00 300.00
2010-11 470.00 NULL
2010-10 470.00 NULL
2010-09 470.00 NULL
2010-08 470.00 NULL
2010-07 470.00 NULL
2010-06 470.00 NULL
2010-05 470.00 NULL
2010-04 470.00 NULL
2010-03 470.00 NULL
2010-02 470.00 NULL
2010-01 470.00 NULL
NULL NULL 470.00
|
Последняя строка, почему-то NULL в колонке даты. А ее там, по всей логике (известной мне) не должно быть. | |
| |
|
|
| |
|
|
| |
для: tAleks
(14.01.2011 в 23:32)
| | | Все верно, так как personal.date не везде есть, нужно использовать значение из первой таблицы с месяцами. | |
| |
|
|
| |
|
|
| |
для: cheops
(14.01.2011 в 23:52)
| | | Понял. Спасибо!
Еще вопрос. Если типов будет не 2, а к примеру 10 или 20, и нужно будет выдать широкую таблицу со всеми 10 или 20 колонками, такой способ ведь уже не подойдет. Как в таком случае можно будет реализовать задуманное? | |
| |
|
|
| |
|
|
| |
для: cheops
(14.01.2011 в 23:18)
| | |
SELECT
m.date,
personal.volume AS p_volume,
organization.volume AS o_volume
FROM
(
SELECT DISTINCT DATE_FORMAT(date_order, '%Y-%m') AS date
FROM users_orders
WHERE id_user = 1134
) AS m
LEFT JOIN (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = 1134
AND type = 'personal'
GROUP BY date
) AS personal ON m.date = personal.date
LEFT JOIN (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = 1134
AND type = 'organization'
GROUP BY date
) AS organization ON m.date = organization.date
GROUP BY m.date
ORDER BY m.date
|
Так наверное будет лучше.
USING - материя достаточно тонкая. | |
| |
|
|
| |
|
|
| |
для: Trianon
(14.01.2011 в 23:47)
| | | А зачем в результирующей таблице "GROUP BY m.date"? Без него можно обойтись? | |
| |
|
|
| |
|
|
| |
для: tAleks
(15.01.2011 в 12:14)
| | | Для подстраховки, да, здесь можно обойтись. Но если бы в условии USING() было другое условие, то без GROUP BY было бы не обойтись. | |
| |
|
|
| |
|
|
| |
для: cheops
(15.01.2011 в 12:40)
| | | Например?
Приведите пожалуйста пример, когда в данном случае без GROUP BY было бы не обойтись.
Спасибо. | |
| |
|
|
| |
|
|
| |
для: tAleks
(15.01.2011 в 16:33)
| | | В данном случае, вероятно привести нельзя, так как изначально все вертится вокруг дат, которые жестко диктуются первой таблицей и через которые связаны все генерируемые таблицы. А вот если бы в одном из случаев условие USING() или ON() вообще не использовались бы, тогда бы в результирующую таблицу вывалилось бы полное декартово произведение. Чтобы исключить дублирующие строки пришлось бы использовать GROUP BY. | |
| |
|
|
| |
|
|
| |
для: cheops
(15.01.2011 в 16:48)
| | | Всем спасибо за все ответы и помощь.
Еще вот такой вопрос.
Если типов будет не 2, а к примеру 10 или 20, и нужно будет выдать широкую таблицу со всеми 10 или 20 колонками, такой способ ведь уже не подойдет. Как в таком случае можно будет реализовать задуманное? | |
| |
|
|
| |
|
|
| |
для: tAleks
(15.01.2011 в 17:27)
| | | Ограничение по длине JOIN-цепочки - минимум 32, и сейчас по-моему 64.
Если в него не влезть, или запрос становится слишком тяжелым,
то выдаются отдельные запросы на каждый месяц.
Они и строятся проще и обрабатываются быстрее. | |
| |
|
|
| |
|
|
| |
для: tAleks
(15.01.2011 в 12:14)
| | | Лишний, конечно.
скопипастил, не посмотрев. | |
| |
|
|
| |
|
|
| |
для: tAleks
(14.01.2011 в 22:05)
| | | создайте таблицу с месяцами (необязатльно реальную,
можно SELECT '2011-01' AS date UNION SELECT '2011-02' UNION ... )
и к ней уже двумя LEFT JOIN'ами приклейте выборки. | |
| |
|
|
| |
|
|
| |
для: Trianon
(14.01.2011 в 22:12)
| | | Я так понимаю тут весь сыр бор из-за постраничной навигации, поэтому такая таблица должна содержать столько месяцев, сколько реально есть в наличие. Т.е. эту первую таблицу нужно формировать как-то так
SELECT DATE_FORMAT(date_order, '%Y-%m') AS date FROM users_orders
WHERE id_user = $this->id_user
GROUP BY date
|
| |
| |
|
|
| |
|
|
| |
для: cheops
(14.01.2011 в 22:19)
| | | Да, если этот набор ограничен записями в тоаблице, тогда конечно.
Тем не менее SELECT DISTINCT с Вашего позволения.
SELECT DISTINCT DATE_FORMAT(date_order, '%Y-%m') AS date
FROM users_orders
WHERE id_user = $this->id_user
|
GROUP BY вне агрегатного запроса мне мерзок и противен естеству и логике :) | |
| |
|
|
| |
|
|
| |
для: tAleks
(14.01.2011 в 22:05)
| | | Вот такой запрос выполняется корректно?
<?php
$sql = "
SELECT
monthes.date,
personal.volume AS p_volume,
organization.volume AS o_volume
FROM
(
SELECT DATE_FORMAT(date_order, '%Y-%m') AS date
FROM users_orders
WHERE id_user = $this->id_user
GROUP BY date
) AS monthes
LEFT JOIN (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = $this->id_user
AND type = 'personal'
GROUP BY date
) AS personal USING(date)
LEFT JOIN (
SELECT
DATE_FORMAT(date_order, '%Y-%m') AS date,
SUM(volume) AS volume
FROM users_orders
WHERE id_user = $this->id_user
AND type = 'organization'
GROUP BY date
) AS organization USING(date)
GROUP BY monthes.date
ORDER BY $this->order $this->sort
LIMIT $this->start, $this->numrec";
?>
|
| |
| |
|
|
|
