fixin (fixin) wrote,
fixin
fixin

Categories:

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

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 истина, суммируются данные из таблицы Бонусы, когда ложь - из таблицы Товары. Все отдельно друг от друга, получается как раз таблица, которую я указывал для решения.
При этом изменений в исходный сложный текст запроса по Товары не требуется.
Каково?



Лобовое решение в виде запроса не прокатывает:


SELECT Товар, ВидБонуса, Себестоимость, IsNULL(Бонус,0)
FROM Товары
  LEFT JOIN Бонусы
 
ON Товары.Товар = Бонусы.Товар & Товары.Период = Бонусы.Период


Т.к. записи дублируюся  и получается так:



Включаем мозг, думаем, решение простое.

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

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 63 comments
Previous
← Ctrl ← Alt
Next
Ctrl → Alt →
Previous
← Ctrl ← Alt
Next
Ctrl → Alt →