June 4th, 2014

promo fixin december 31, 2037 16:57 1420
Buy for 30 tokens
UPD: Друзья, в августе 2019 года блог переехал на http://fixinchik.ru. Welcome! Добро пожаловать в журнал Осипова Сергея Александровича, известного также как Fixin и Гений 1С. Рекомендую ознакомиться с Часто Задаваемыми Вопросами обо мне. Что я хочу в подарок - список. Мой проект "…
против жуликов и воров

Гуру-клас по запросам суммирования

UPD: Решение задачи под спойлером (пока промежуточное, думайте до конца)

[Spoiler (click to open)]Для того, чтобы отчет правильно нарисовался, достаточно получить такую таблицу данных:

Товар / ВидБонуса/ Себестоимость/ Бонус
Компьютер Квант, NULL, 1000, 0
Компьютер Квант, Бонус продавца, 0, 20
Компьютер Квант, Бонус менеджера, 0, 100
Монитор Соник, NULL, 1200, 0
Монитор Соник, Бонус менеджера, 0, 200


В принципе, задача решается через UNION, но в исходном запросе было очень много колонок, а язык SQL не позволяет, к сожалению, указать что нужно добавлять только определенные колонки, а остальные оставлять нулями. К тому же, исходный запрос тоже был довольно сложным и вмешательство в него было бы себе дороже.

Кто-то предлагал использовать Максимум или сумму делить на количество. Это извращение, не говоря уже о погрешности суммирования. Нет, такой метод не годится.

Более практичным и компактным по коду оказалось решение через JOIN.

Тем не менее, те, кто думают, что такой запрос выдаст правильный ответ, ошибаются:
SELECT Товар, ВидБонуса, CASE Бонус IS NULL Себестоимость ELSE 0 END, IsNULL(Бонус,0)
FROM Товары
  LEFT JOIN Бонусы
 
ON Товары.Товар = Бонусы.Товар & Товары.Период = Бонусы.Период
В чем ошибка? Если бонусов для Товар + Период нет, тогда себестоимость будет считаться, иначе она всегда будет равна нулевой.
Поэтому в таком соединении себестоимость нужно все же считать, если соединения с таблицей нет, или это первая из соединяемых записей. К сожалению, в SQL нет конструкции "первая из соединяемых".
Но тем не менее, решение можно найти. В нем вся изюминка данной задачи. Думаем, знатоки.
Подсказываю - достаточно сделать еще один JOIN.


Предположим у вас есть таблица Продажи с полями Дата, Товар, Себестоимость   и таблица Бонусы с полями ВидБонуса, Дата, Товар, Бонус.


Нужно вывести отчет вида:



Тут компьютер и монитор - товары, Себестоимость - поле таблицы Продажи, Бонус - поле таблицы Бонусы. Виды бонуса: бонус продавца, бонус менеджера.


В решении предоставить просто текст запроса, без всяких хитростей СКД.


Группировки, как понятно из картинки - Товар, ВидБонуса. Суммируются себестоимость и Бонус.


UPD2: Решение, изящное, как лобок девственницы.

[Spoiler (click to open)]

SELECT Товар, ВидБонуса, CASE Semafor.Flag  0 ELSE Себестоимость END,  CASE Semafor.Flag  Бонус ELSE 0 END
FROM Товары
LEFT JOIN (SELECT True As Flag UNION SELECT False) As Semafor ON TRUE
  LEFT JOIN Бонусы
 
ON Товары.Товар = Бонусы.Товар & Товары.Период = Бонусы.Период

Используется служебная таблица Semafor с двумя строчками в колонке Flag.
Когда Flag истина, суммируются данные из таблицы Бонусы, когда ложь - из таблицы Товары. Все отдельно друг от друга, получается как раз таблица, которую я указывал для решения.
При этом изменений в исходный сложный текст запроса по Товары не требуется.
Каково?


Collapse )