Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Фирма вторсырья»

В предположении, что приход и расход денег на каждом пункте приема фиксируется произвольное число раз (в обе таблицы добавлен первичный ключ 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;

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;

    Схема

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

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

Консоль

Выполнить

SELECT * FROM Income 
WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

SELECT * FROM Income WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

    Схема

Консоль

Выполнить

SELECT * FROM Outcome 
WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

SELECT * FROM Outcome WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

    Схема

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

Приход

Расход

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

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

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

ПиР