Упражнение 30
В этой задаче требуется данные из двух таблиц собрать в одном результирующем наборе; при этом приход и расход денег на пункте приема в один и тот же день должны находиться в одной строке.
Аналогичная задача (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;
[[ column ]] |
---|
[[ value ]] |
Идея решения такова. Выполняется соединение таблицы, в которой фиксируются приходы денег, с таблицей расходов средств по совпадению номера пункта приема и даты. Левое соединение, которое здесь используется, гарантирует получение результата в том случае, если на пункте приема в некоторые дни есть только приход, но нет расхода (NULL). Далее выполняется объединение с запросом, в котором выполняется обратное левое соединение таблицы расхода с таблицей прихода. Таким образом, учитывается случай, когда на пункте есть расход, но нет прихода. Исключение дубликатов строк (в случаях, когда есть и приход, и расход) выполняется использованием оператора UNION.
Запрос 2.1.1 дает неверный результат, когда в один день на пункте приема выполняется несколько операций по приходу/расходу денежных средств. В качестве примера возьмем характерный для этого случая день — 24 марта 2001 года. Выполним пару запросов:
SELECT * FROM Income
WHERE date = '2001-03-24 00:00:00.000' AND point = 1;
[[ column ]] |
---|
[[ value ]] |
SELECT * FROM Outcome
WHERE date = '2001-03-24 00:00:00.000' AND point = 1;
[[ column ]] |
---|
[[ value ]] |
Получим следующий результат:
Приход
code | point | date | inc |
---|---|---|---|
3 | 1 | 2001-03-24 00:00:00.000 | 3600 |
11 | 1 | 2001-03-24 00:00:00.000 | 3400 |
Расход
code | point | date | out |
---|---|---|---|
2 | 1 | 2001-03-24 00:00:00.000 | 3663 |
13 | 1 | 2001-03-24 00:00:00.000 | 3500 |
В данном случае, когда есть и приход, и расход, внешнее соединение эквивалентно внутреннему соединению, то есть каждая строка из одной таблицы соединяется с каждой строк из другой таблицы, если в этих строках совпадают и дата, и номер пункта приема. Поэтому перед группировкой будет получен следующий результат (показаны только столбцы прихода и расхода):
inc | out |
---|---|
3600 | 3663 |
3600 | 3500 |
3400 | 3663 |
3400 | 3500 |
После группировки и суммирования мы получаем удвоение результата, как для прихода, так и для расхода. Если бы прихода было три, то мы бы получили утроение расхода и т. д.
И дубликаты здесь ни при чем, так как каждый из объединяемых запросов дает аналогичный результат, то есть остается одна строка на каждую пару значений {пункт, дата}.