Упражнение 30

В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз (в обе таблицы добавлен первичный ключ code), написать запрос с выходными данными (point, date, out, inc), в котором каждому пункту за каждую дату соответствует одна строка.

В этой задаче требуется данные из двух таблиц собрать в одном результирующем наборе; при этом приход и расход денег на пункте приема в один и тот же день должны находиться в одной строке.

Аналогичная задача (29) для таблиц Income_o и Outcome_o, как правило, затруднений не вызывала. Суть проблемы демонстрирует следующее решение.

Решение 2.1.1

SELECT Income.point, Income.date, SUM(out), SUM(inc)
FROM Income 
    LEFT JOIN Outcome ON Income.point = Outcome.point 
                      AND Income.date = Outcome.date
GROUP BY Income.point, Income.date
UNION
SELECT Outcome.point, Outcome.date, SUM(out), SUM(inc)
FROM Outcome 
    LEFT JOIN Income ON Income.point = Outcome.point 
                     AND Income.date = Outcome.date
GROUP BY Outcome.point, Outcome.date;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Идея решения такова. Выполняется соединение таблицы, в которой фиксируются приходы денег, с таблицей расходов средств по совпадению номера пункта приема и даты. Левое соединение, которое здесь используется, гарантирует получение результата в том случае, если на пункте приема в некоторые дни есть только приход, но нет расхода (NULL). Далее выполняется объединение с запросом, в котором выполняется обратное левое соединение таблицы расхода с таблицей прихода. Таким образом, учитывается случай, когда на пункте есть расход, но нет прихода. Исключение дубликатов строк (в случаях, когда есть и приход, и расход) выполняется использованием оператора UNION.

Запрос 2.1.1 дает неверный результат, когда в один день на пункте приема выполняется несколько операций по приходу/расходу денежных средств. В качестве примера возьмем характерный для этого случая день — 24 марта 2001 года. Выполним пару запросов:

SELECT * FROM Income
WHERE date = '2001-03-24 00:00:00.000' AND point = 1;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
SELECT * FROM Outcome
WHERE date = '2001-03-24 00:00:00.000' AND point = 1;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Получим следующий результат:

Приход

codepointdateinc
312001-03-24 00:00:00.0003600
1112001-03-24 00:00:00.0003400

Расход

codepointdateout
212001-03-24 00:00:00.0003663
1312001-03-24 00:00:00.0003500

В данном случае, когда есть и приход, и расход, внешнее соединение эквивалентно внутреннему соединению, то есть каждая строка из одной таблицы соединяется с каждой строк из другой таблицы, если в этих строках совпадают и дата, и номер пункта приема. Поэтому перед группировкой будет получен следующий результат (показаны только столбцы прихода и расхода):

incout
36003663
36003500
34003663
34003500

После группировки и суммирования мы получаем удвоение результата, как для прихода, так и для расхода. Если бы прихода было три, то мы бы получили утроение расхода и т. д.

И дубликаты здесь ни при чем, так как каждый из объединяемых запросов дает аналогичный результат, то есть остается одна строка на каждую пару значений {пункт, дата}.

ПиР

Решить задачу на SQL-EX.RU