loading..
Русский    English
13:53
листать

Упражнение 30

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

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

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

Решение 2.1.1

Консоль
Выполнить
  1. SELECT Income.point, Income.date, SUM(out), SUM(inc)
  2. FROM Income LEFT JOIN
  3. Outcome ON Income.point = Outcome.point AND
  4. Income.date = Outcome.date
  5. GROUP BY Income.point, Income.date
  6. UNION
  7. SELECT Outcome.point, Outcome.date, SUM(out), SUM(inc)
  8. FROM Outcome LEFT JOIN
  9. Income ON Income.point = Outcome.point AND
  10. Income.date = Outcome.date
  11. GROUP BY Outcome.point, Outcome.date;

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

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

Консоль
Выполнить
  1. SELECT * FROM Income
  2. WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

Консоль
Выполнить
  1. SELECT * FROM Outcome
  2. WHERE date = '2001-03-24 00:00:00.000' AND point = 1;

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

Приход

code point date inc
3 1 2001-03-24 00:00:00.000 3600.0000
11 1 2001-03-24 00:00:00.000 3400.0000

Расход

code point date out
2 1 2001-03-24 00:00:00.000 3663.0000
13 1 2001-03-24 00:00:00.000 3500.0000

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

inc out
3600.0000 3663.0000
3600.0000 3500.0000
3400.0000 3663.0000
3400.0000 3500.0000

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

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

ПиР

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100