|
Модераторы: skyboy |
|
JEEN |
|
||||
Шустрый Профиль Группа: Участник Сообщений: 88 Регистрация: 8.3.2008 Репутация: 1 Всего: 1 |
Здравствуйте. Имеется запрос, который должен выводить счета, попутно считая общую сумму по всем наименованиям и оплаченную сумму.
Например, имеем 2 счета: 1) Счет №1 (2 наименования на общую сумму 2000 руб.), был 1 платеж на всю сумму. 2) Счет №2 (1 наименование на сумму 500 руб.), по ошибке было 2 платежа по 500 руб. Должно вывести так: Счет №1 | 2 услуги | 1 платеж | 2000 руб. (сумма счета) | 2000 руб. (оплачено) Счет №2 | 1 услуга | 2 платежа | 500 руб. (сумма счета) | 1000 руб. (оплачено) А сейчас выводит так: Счет №1 | 2 услуги | 1 платеж | 2000 руб. (сумма счета) | 4000 руб. (оплачено) Счет №2 | 1 услуга | 2 платежа | 500 руб. (сумма счета) | 1000 руб. (оплачено) Ошибка в том, что он почему то количество услуг (наименований) умножил на сумму счета. Запрос:
Либо так: Счет №1 | 2 услуги | 1 платеж | 2000 руб. (сумма счета) | 2000 руб. (оплачено) Счет №2 | 1 услуга | 2 платежа | 500 руб. (сумма счета) | 500 руб. (оплачено) Если добавить еще один DISTINCT, то теперь он не учитывает 2 платежа по счету. Запрос:
Упрощенная структура базы данных выглядит так: Это сообщение отредактировал(а) JEEN - 18.3.2016, 01:09 |
||||
|
|||||
JEEN |
|
|||
Шустрый Профиль Группа: Участник Сообщений: 88 Регистрация: 8.3.2008 Репутация: 1 Всего: 1 |
Попробовал с подзапросами сделать, результат на первый взгляд верный, но может быть возможно сделать это в один запрос?
Это сообщение отредактировал(а) JEEN - 18.3.2016, 01:43 |
|||
|
||||
Akina |
|
||||||
Советчик Профиль Группа: Модератор Сообщений: 20570 Регистрация: 8.4.2004 Где: Зеленоград Репутация: 45 Всего: 453 |
Ну это ТВОЯ ошибка. Которая проистекает из непонимания того, как выполняется JOIN. У тебя две записи в payments и две в details - в результате после JOIN ты получишь четыре объединённых записи. Отсюда и кратное увеличение суммы.
Интересно, как это он дал верный результат БЕЗ ГРУППИРОВКИ? Вообще решение с подзапросами верное, но скорее всего медленное - не всегда коррелированные подзапросы обрабатываются правильно, зачастую они выполняются для каждой записи основной таблицы, что есть неправильно. Более адекватным будет запрос типа (пишу от руки, могу накосячить)
-------------------- О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума. |
||||||
|
|||||||
tzirechnoy |
|
|||
Эксперт Профиль Группа: Завсегдатай Сообщений: 1173 Регистрация: 30.1.2009 Репутация: 2 Всего: 16 |
Да большынство СУБД ужэ научилось всё это правильно преобразовывать в единый план. Более того, шансы оптимизатору ошыбиться как раз большэ при наличии группировок во вложэнных запросах, из-за которых он не можэт произвольно менять начальную таблицу. Впрочем, при LEFT JOin это так и так ему сложно. В итоге -- тривиальный план с проходом по таблицэ invoices и индэксной выборкой из payments и details будет правильным чуть менее чем всегда, так что за скорость особенно можно не волноваться. |
|||
|
||||
Akina |
|
|||
Советчик Профиль Группа: Модератор Сообщений: 20570 Регистрация: 8.4.2004 Где: Зеленоград Репутация: 45 Всего: 453 |
К сожалению, ТС не озвучил СУБД, не говоря уж о версии. -------------------- О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума. |
|||
|
||||
tzirechnoy |
|
||||
Эксперт Профиль Группа: Завсегдатай Сообщений: 1173 Регистрация: 30.1.2009 Репутация: 2 Всего: 16 |
Да, за такое использование DISTINCT надо сразу расстреливать по-моему. Разные значения стоимости позицыи вообще никак не относятся к разным позицыям. Ну, то есть у одной позицыи стоимость будет одинакова, конечно, но вот у разных она тожэ можэт быть одинаковой. Правильное приближэние -- поделить sum() на количество "неправильных" повторений, т.е. количество записей из другой таблицы, которые были использованы в данной группе. В частности, sum(d.cost*d.amount) / count(DISTINCT p.id), и sum(p.sum) / count(DISTINCT d.id). Единственно, возникнет проблема с LEFT JOIN -- когда записей в другой таблицэ нет, но ведь одно повторение осталось! Так что потребуется вместо count(DISTINCT p.id) взять что-нибудь вроде count(DISTINCT COALESCE(p.id, -1)) чтобы всегда NULL было заменено на (невозможное в других случаях) значение, и, таким образом, это повторение данных посчиталось, ну или написать CASE для замены 0 на 1: CASE WHEN count(DISTINCT p.id) = 0 THEN 1 ELSE count(DISTINCT p.id) END. И, естественно, тожэ самое для другой таблицы. Добавлено через 3 минуты и 31 секунду
MySQL, надо думать, кто ещё позволит в SELECT указать колонку, которой в GROUP BY не было? |
||||
|
|||||
JEEN |
|
|||
Шустрый Профиль Группа: Участник Сообщений: 88 Регистрация: 8.3.2008 Репутация: 1 Всего: 1 |
Всем спасибо за помощь. Вчера еще перенес свой код на рабочий проект, работает он без группировки отлично, точно так, как я написал во втором сообщении.
БД MySQL 5.5.47 |
|||
|
||||
Akina |
|
|||
Советчик Профиль Группа: Модератор Сообщений: 20570 Регистрация: 8.4.2004 Где: Зеленоград Репутация: 45 Всего: 453 |
tzirechnoy, вот видишь... а ты
Вот когда он до 5,7 обновится - тогда ещё на что-то можно будет надеяться. -------------------- О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума. |
|||
|
||||
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей) | |
0 Пользователей: | |
« Предыдущая тема | Составление SQL-запросов | Следующая тема » |
|
По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности Powered by Invision Power Board(R) 1.3 © 2003 IPS, Inc. |