Уже пару дней воюю с проблемой, пробовал так и эдак, перелопатил кучу инфы, но что-то тяму не хватает. Поможите пожалуйста, люди добрые!
В принципе, суть задачи проста: есть несколько разных таблиц, в которых есть одинаковые поля. Допустим, это таблицы table_1 и table_2. В каждой из них есть поля user_id, quant, cost. user_id - это поле, описывающее, к кому относится запись, quant и cost - конкретные параметры данной записи.
Для примера:
--------------------
table_1: (user_id|quant|cost)
1 1 2
2 2 4
1 1 1
table_2: (user_id|quant|cost)
3 1 3
2 1 1
3 2 1
users: (id|name)
1 vasya
2 fedya
3 misha
--------------------
мне нужно сделать выборку из обоих таблиц с группировкой по id пользователя (но вместо id нужно подставить имя). При этом величины из столбцов quant, cost нужно суммировать. Т.е. результатом выборки должна стать таблица:
vasya 2 3
fedya 3 5
misha 3 4
т.е. для каждого пользователя считается суммарное значение столбцов quant и суммарное значение столбцов cost из всех таблиц.
Помогите пожалуйста грамотно составить MySQL запрос, который это дело реализует. Пробовал сделать что-то вроде:
SELECT `users`.`name`, (SUM(`table_1`.`quant`) + SUM(`table_2`.`quant`)) AS aggregate_quant, (SUM(`table_1`.`cost`) + SUM(`table_2`.`cost`)) AS aggregate_cost
FROM `users`, `table_1`, `table_2`
WHERE (`table_1`.`user_id` = `users`.`id` || `table_2`.`user_id` = `users`.`id`)
GROUP BY (`users`.`name`)
в самых разнообразных вариациях. Ноль эффекта. У меня уже мозга за мозгу заходит.
Спустя 7 часов, 8 минут, 46 секунд (4.05.2008 - 12:38) Ghost написал(а):
http://phpclub.ru/mysql/doc/union.html и вложенный запрос тебе помогут
Спустя 17 часов, 52 минуты, 59 секунд (5.05.2008 - 06:31) hydrogen написал(а):
Спасибо я думал над этим еще до того, как сюда написать, но покопавшись в инете вычитал, что в MySQL якобы не поддерживаются вложенные запросы (или поддерживаются только с 4й версии - точно не помню). Ни у кого случайно нет ссылочки на дельную статейку по вложенным запросам в MySQL?
я думал над этим еще до того, как сюда написать, но покопавшись в инете вычитал, что в MySQL якобы не поддерживаются вложенные запросы (или поддерживаются только с 4й версии - точно не помню). Ни у кого случайно нет ссылочки на дельную статейку по вложенным запросам в MySQL? Спустя 3 часа, 39 минут, 20 секунд (5.05.2008 - 10:11) Ghost написал(а):
врядли ты где-нить найдёшь сейчас версию ниже 4-й...
http://www.mysql.ru/search/?b=1&terms=...%EF%F0%EE%F1%FB
http://www.mysql.ru/search/?b=1&terms=...%EF%F0%EE%F1%FB
Спустя 4 часа, 35 минут, 35 секунд (5.05.2008 - 14:46) hydrogen написал(а):
Цитата(Ghost @ 5.5.2008, 7:11) [snapback]37926[/snapback]
врядли ты где-нить найдёшь сейчас версию ниже 4-й...
http://www.mysql.ru/search/?b=1&terms=...%EF%F0%EE%F1%FB
http://www.mysql.ru/search/?b=1&terms=...%EF%F0%EE%F1%FB
Спасибо.
Все получилось. Решить задачу удалось двумя способами: при помощи связки UNION + вложенный запрос, а так же вложенного запроса с JOIN-ом.
Спустя 3 часа, 13 минут, 42 секунды (5.05.2008 - 18:00) Ghost написал(а):
высвети решение с джоинами... а то грызут меня сомнения...
Спустя 17 часов, 23 минуты, 11 секунд (6.05.2008 - 11:23) hydrogen написал(а):
Мне этот вариант показал товарисч Akina с Вингарда. Я его только маленько переделал:
SELECT users.name,
(q1.Sum-quant+q2Sum-quant) AS AllQuant,
(q1.Sum-cost+q2.Sum-cost) AS AllCost
FROM
(
SELECT table2.user_id,
Sum(table2.quant) AS Sum-quant,
Sum(table2.cost) AS Sum-cost
FROM table2
WHERE table2.cost IS NOT NULL
GROUP BY table2.user_id
) As q2
RIGHT JOIN
(
(
SELECT table1.user_id,
Sum(table1.quant) AS Sum-quant,
Sum(table1.cost) AS Sum-cost
FROM table1
WHERE table1.cost IS NOT NULL
GROUP BY table1.user_id
) As q1
RIGHT JOIN
users
ON q1.user_id = users.id
)
ON q2.user_id = users.id;
все вроде работает
SELECT users.name,
(q1.Sum-quant+q2Sum-quant) AS AllQuant,
(q1.Sum-cost+q2.Sum-cost) AS AllCost
FROM
(
SELECT table2.user_id,
Sum(table2.quant) AS Sum-quant,
Sum(table2.cost) AS Sum-cost
FROM table2
WHERE table2.cost IS NOT NULL
GROUP BY table2.user_id
) As q2
RIGHT JOIN
(
(
SELECT table1.user_id,
Sum(table1.quant) AS Sum-quant,
Sum(table1.cost) AS Sum-cost
FROM table1
WHERE table1.cost IS NOT NULL
GROUP BY table1.user_id
) As q1
RIGHT JOIN
users
ON q1.user_id = users.id
)
ON q2.user_id = users.id;
все вроде работает
Спустя 1 день, 22 часа, 42 минуты, 38 секунд (8.05.2008 - 10:06) Ghost написал(а):
мысль понятна, но.. выложи пожалйста всё-таки дамп таблиц и точный sql-запрос, или хотяб только запрос, но точный - этот у меня просто ошибку выдает.
дело в том что есть ряд нюансов, касаемо работы мускля с NULL
запрос select 1+null вернёт не 1, а null
а твоё условие where в даном случае относится к непосредственно исходной таблице, и скорее всего смысла вообще не имеет, т.к будет всегда истинным
можно конешно заюзать не просто сумму q1.Sum-quant+q2Sum-quant, а что-то типа GREATEST(sq1+sq2, sq1, sq2)
тогда получится.. но оптимальность этого варианта сомнительна..
дело в том что есть ряд нюансов, касаемо работы мускля с NULL
запрос select 1+null вернёт не 1, а null
а твоё условие where в даном случае относится к непосредственно исходной таблице, и скорее всего смысла вообще не имеет, т.к будет всегда истинным
можно конешно заюзать не просто сумму q1.Sum-quant+q2Sum-quant, а что-то типа GREATEST(sq1+sq2, sq1, sq2)
тогда получится.. но оптимальность этого варианта сомнительна..
Спустя 1 день, 3 часа, 50 минут, 34 секунды (9.05.2008 - 13:56) hydrogen написал(а):
Вообще, я воспользовался вариантом с UNION-ами, поэтому проблем не возникло.
Однако:
Маленько поковырявшись с тем запросом, я понял, что ты прав.
Не совсем так. В изначальном варианте таблицы в столбцах quant и cost были возможны значения NULL (но только ОБА NULL - сразу: и quant, и cost), это уже потом я изменил структуру и поставил 0.0 по дефалту. Так что теперь этот WHERE действительно можно отбросить.
В точку. В случае, когда в одной из таблиц записей, по одному из операторов не находится, получаем 'some_value_from_table_N + NULL' - и общий привет в итоге. Что-то я не подумал, что пустой результат - тоже NULL. К слову говоря, в исходном варианте (как мне его показали), там была такая проверка. Если интересно, см. тут
Эээ... вообще, это тяжко, т.к. реальные таблицы выглядят совсем по-другому. Так скажем, я все сильно утрировал для наглядности. Но сейчас попробую
Таблицы:
Запрос:
В результате:
Нули, чего мы так и опасались...
По моему проще сделать проверку так:
По моему, так быстрее будет.
Однако:
Цитата
дело в том что есть ряд нюансов, касаемо работы мускля с NULL
запрос select 1+null вернёт не 1, а null
запрос select 1+null вернёт не 1, а null
Маленько поковырявшись с тем запросом, я понял, что ты прав.
Цитата
а твоё условие where ... смысла вообще не имеет, т.к будет всегда истинным
Не совсем так. В изначальном варианте таблицы в столбцах quant и cost были возможны значения NULL (но только ОБА NULL - сразу: и quant, и cost), это уже потом я изменил структуру и поставил 0.0 по дефалту. Так что теперь этот WHERE действительно можно отбросить.
Цитата
условие where в даном случае относится к непосредственно исходной таблице
В точку. В случае, когда в одной из таблиц записей, по одному из операторов не находится, получаем 'some_value_from_table_N + NULL' - и общий привет в итоге. Что-то я не подумал, что пустой результат - тоже NULL. К слову говоря, в исходном варианте (как мне его показали), там была такая проверка. Если интересно, см. тут
Цитата
выложи пожалйста всё-таки дамп таблиц и точный sql-запрос
Эээ... вообще, это тяжко, т.к. реальные таблицы выглядят совсем по-другому. Так скажем, я все сильно утрировал для наглядности. Но сейчас попробую
Таблицы:
Код
--
-- Структура таблицы `table_1`
--
CREATE TABLE `table_1` (
`id` int(11) unsigned NOT NULL auto_increment,
`user_id` smallint(10) unsigned NOT NULL default '1',
`quant` float unsigned default '0',
`cost` float unsigned default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=4;
--
-- Дамп данных таблицы `table_1`
--
INSERT INTO `table_1` VALUES (1, 1, 1, 2);
INSERT INTO `table_1` VALUES (2, 2, 2, 4);
INSERT INTO `table_1` VALUES (3, 1, 1, 1);
-- --------------------------------------------------------
--
-- Структура таблицы `table_2`
--
CREATE TABLE `table_2` (
`id` int(11) unsigned NOT NULL auto_increment,
`user_id` smallint(10) unsigned NOT NULL default '1',
`quant` float unsigned default '0',
`cost` float unsigned default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=7;
--
-- Дамп данных таблицы `table_2`
--
INSERT INTO `table_2` VALUES (4, 3, 1, 3);
INSERT INTO `table_2` VALUES (5, 2, 1, 1);
INSERT INTO `table_2` VALUES (6, 3, 2, 1);
-- --------------------------------------------------------
--
-- Структура таблицы `users`
--
CREATE TABLE `users` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=4;
--
-- Дамп данных таблицы `users`
--
INSERT INTO `users` VALUES (1, 'vasya');
INSERT INTO `users` VALUES (2, 'fedya');
INSERT INTO `users` VALUES (3, 'misha');
-- Структура таблицы `table_1`
--
CREATE TABLE `table_1` (
`id` int(11) unsigned NOT NULL auto_increment,
`user_id` smallint(10) unsigned NOT NULL default '1',
`quant` float unsigned default '0',
`cost` float unsigned default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=4;
--
-- Дамп данных таблицы `table_1`
--
INSERT INTO `table_1` VALUES (1, 1, 1, 2);
INSERT INTO `table_1` VALUES (2, 2, 2, 4);
INSERT INTO `table_1` VALUES (3, 1, 1, 1);
-- --------------------------------------------------------
--
-- Структура таблицы `table_2`
--
CREATE TABLE `table_2` (
`id` int(11) unsigned NOT NULL auto_increment,
`user_id` smallint(10) unsigned NOT NULL default '1',
`quant` float unsigned default '0',
`cost` float unsigned default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=7;
--
-- Дамп данных таблицы `table_2`
--
INSERT INTO `table_2` VALUES (4, 3, 1, 3);
INSERT INTO `table_2` VALUES (5, 2, 1, 1);
INSERT INTO `table_2` VALUES (6, 3, 2, 1);
-- --------------------------------------------------------
--
-- Структура таблицы `users`
--
CREATE TABLE `users` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=4;
--
-- Дамп данных таблицы `users`
--
INSERT INTO `users` VALUES (1, 'vasya');
INSERT INTO `users` VALUES (2, 'fedya');
INSERT INTO `users` VALUES (3, 'misha');
Запрос:
Код
SELECT users.name,
q1.SumQuant + q2.SumQuant AS AllQuant,
q1.SumCost + q2.SumCost AS AllCost
FROM
(
SELECT table_1.user_id,
Sum(table_1.quant) AS SumQuant,
Sum(table_1.cost) AS SumCost
FROM table_1
GROUP BY table_1.user_id
) As q2
RIGHT JOIN
(
(
SELECT table_2.user_id,
Sum(table_2.quant) AS SumQuant,
Sum(table_2.cost) AS SumCost
FROM table_2
GROUP BY table_2.user_id
) As q1
RIGHT JOIN
users
ON q1.user_id = users.id
)
ON q2.user_id = users.id;
q1.SumQuant + q2.SumQuant AS AllQuant,
q1.SumCost + q2.SumCost AS AllCost
FROM
(
SELECT table_1.user_id,
Sum(table_1.quant) AS SumQuant,
Sum(table_1.cost) AS SumCost
FROM table_1
GROUP BY table_1.user_id
) As q2
RIGHT JOIN
(
(
SELECT table_2.user_id,
Sum(table_2.quant) AS SumQuant,
Sum(table_2.cost) AS SumCost
FROM table_2
GROUP BY table_2.user_id
) As q1
RIGHT JOIN
users
ON q1.user_id = users.id
)
ON q2.user_id = users.id;
В результате:
Код
name AllQuant AllCost
vasya NULL NULL
fedya 3 5
misha NULL NULL
vasya NULL NULL
fedya 3 5
misha NULL NULL
Нули, чего мы так и опасались...
Цитата
можно конешно заюзать не просто сумму q1.Sum-quant+q2Sum-quant, а что-то типа GREATEST(sq1+sq2, sq1, sq2)
тогда получится.. но оптимальность этого варианта сомнительна..
тогда получится.. но оптимальность этого варианта сомнительна..
По моему проще сделать проверку так:
Код
SELECT users.name,
(IF(q1.SumQuant IS NULL, 0, q1.SumQuant) + IF(q2.SumQuant IS NULL, 0, q2.SumQuant)) AS AllQuant,
(IF(q1.SumCost IS NULL, 0, q1.SumCost) + IF(q2.SumCost IS NULL, 0, q2.SumCost)) AS AllCost
FROM
... (ля-ля-ля, три рубля...)
(IF(q1.SumQuant IS NULL, 0, q1.SumQuant) + IF(q2.SumQuant IS NULL, 0, q2.SumQuant)) AS AllQuant,
(IF(q1.SumCost IS NULL, 0, q1.SumCost) + IF(q2.SumCost IS NULL, 0, q2.SumCost)) AS AllCost
FROM
... (ля-ля-ля, три рубля...)
По моему, так быстрее будет.
Спустя 7 минут, 8 секунд (9.05.2008 - 14:03) hydrogen написал(а):
Мда, кстати, еще один вопрос... В-общем, нужная таблица строится у меня так:
Возникла еще одна проблемка: мне теперь надо взять сумму значений уже по этой таблице и добавить полученное хозяйство в конец результата. Т.е. нужна таблица:
vasya 2 3
fedya 3 5
misha 3 4
total 8 12
Как бы это дело провернуть?.. Результаты своих мытарств даже выкладывать не буду - слишком уж они плачевные...
Код
SELECT name, SUM(quant) AS agr_quant, SUM(cost) AS agr_cost
FROM (
SELECT user_id, quant, cost
FROM table_1
UNION ALL
SELECT user_id, quant, cost
FROM table_2
) AS Total
RIGHT JOIN users ON Total.user_id = users.id
WHERE cost IS NOT NULL
GROUP BY user_id
FROM (
SELECT user_id, quant, cost
FROM table_1
UNION ALL
SELECT user_id, quant, cost
FROM table_2
) AS Total
RIGHT JOIN users ON Total.user_id = users.id
WHERE cost IS NOT NULL
GROUP BY user_id
Возникла еще одна проблемка: мне теперь надо взять сумму значений уже по этой таблице и добавить полученное хозяйство в конец результата. Т.е. нужна таблица:
vasya 2 3
fedya 3 5
misha 3 4
total 8 12
Как бы это дело провернуть?.. Результаты своих мытарств даже выкладывать не буду - слишком уж они плачевные...
Спустя 26 минут, 36 секунд (9.05.2008 - 14:30) Ghost написал(а):
боюсь это придётся в скрипте делать. не то, что это принципиально нельзя на мускле сделать, но лучше скриптом.
решение - еще один юнион, с полной свёрткой.
решение - еще один юнион, с полной свёрткой.
Спустя 11 часов, 55 минут, 45 секунд (10.05.2008 - 02:26) hydrogen написал(а):
Да я вот и сам думал, что придется уже во время итерирования по результатам запроса в скрипте суммы обсчитывать... но... некрасиво как-то. Мне тут авторитетные товарищи предложили создавать временную таблицу - и ее заполнять... Но меня терзают смутные сомения - как это скажется на быстродействии? Даже если создавать ее в памяти (я не думаю, что в ближайшие века число пользователей хотя бы приблизится к 100).
Спустя 11 минут, 33 секунды (10.05.2008 - 02:37) Ghost написал(а):
угу.. вот и я о том же. можно мусклом, можно кстати не временную таблицу сделать, а через хранимую процедуру всё это провернуть, но оптимальнее это вынести в пхп, как мне кажется. хотя с хранимой процедурой можно и попробовать.
Спустя 14 часов, 15 минут, 23 секунды (10.05.2008 - 16:53) Ghost написал(а):
о, походу есть еще оджна полезная ф-ция.. можно не
GREATEST(sq1+sq2, sq1, sq2)
а
IFNULL(sq1, 0) + IFNULL(sq2, 0)
но всё равно через юнион оптимальнее
GREATEST(sq1+sq2, sq1, sq2)
а
IFNULL(sq1, 0) + IFNULL(sq2, 0)
но всё равно через юнион оптимальнее
_____________