#sql #sql-server #inner-join
Вопрос:
Попытка объединить две таблицы для отображения совпадающих итогов, однако объединение (или группировка по) создает неправильные суммирования для нескольких записей (не для всех). Я проверил обе таблицы отдельно, чтобы убедиться, что сумма уплаченных налогов совпадает с общей суммой, и они совпадают при просмотре в отдельных таблицах. Похоже, что происходит то, что несколько записей «total1» умножаются на 2 (не знаю, почему).
Таблица 1:
| pnum | ветка | год | весь |
|---|---|---|---|
| 5678 | efgh | 2018 | 2000 |
| 5678 | efgh | 2018 | 2579 |
Таблица 2:
| pnum | ветка | год | оплачиваемый | налог |
|---|---|---|---|---|
| 5678 | efgh | 2018 | 3200 | 900 |
| 5678 | efgh | 2018 | 400 | 79 |
Код:
Select pnum, a.branch, a.year, sum(total) as total, sum(paid tax) as total1
from ##table1 a
inner join ##table2 b
on a.[pnum] = b.pnum2 and a.year = left(b.year,4) and a.branch = b.branch
Group by pnum, a.branch, a.year
Результат выглядит примерно так:
| pnum | ветка | год | весь | итого1 |
|---|---|---|---|---|
| 1234 | abcd | 2020 | 3520 | 3520 |
| 5678 | efgh | 2018 | 4579 | 9158 |
| 9101 | ijkl | 2019 | 2378 | 2378 |
| 1121 | mnop | 2019 | 9436 | 9436 |
Как вы можете видеть, в строке 2 значение «итого1» вдвое превышает значение «итого»…и «итого», и «итого1» должны совпадать.
Любая помощь/совет будут высоко оценены.
Комментарии:
1. Пожалуйста, предоставьте примерные данные. Не очевидно, что результаты неверны.
2. Привет @2020db9 пожалуйста, проверьте значение столбца «Оплачено» и «налог» для строки 2. Возможно, существует неправильное значение.
3. Скорее всего, в одной из таблиц, вероятно, есть повторяющаяся строка
##table2. Выборочные данные и ожидаемые результаты очень помогли бы.4. Развивайте хорошие привычки. Каждая ссылка на столбец должна включать соответствующий псевдоним таблицы (а псевдонимы, такие как «а», тоже являются просто ленивыми привычками). Мы не знаем, какая таблица содержит какой столбец, но ваша логика, по-видимому, предполагает, что между таблицами существует соотношение 1:1. В противном случае не было бы никаких «дубликатов», и ваши итоги были бы правильными. Возможно, вы сможете ОБЪЕДИНИТЬ ВСЕ таблицы, а затем суммировать этот набор результатов, но это рискованное предположение без DDL и выборочных данных.
5. может ли быть проблема с суммой(уплачено налог) ? Я отредактировал свой первоначальный пост и включил примеры исходных данных
Ответ №1:
Это нормальное поведение соединения, когда отношения не являются взаимно однозначными. Возможно, у вас есть более одного совпадения в каждой таблице. Вы можете сделать это несколькими способами. Вот один, который суммирует каждую таблицу отдельно, а затем объединяет результат. Это соотношение (между cte1 и cte2) теперь один к одному. Я игнорирую случаи, когда записи в одной таблице могут не совпадать с записями в другой.
WITH cte1 AS (
SELECT pnum, branch, year, SUM(total) AS total1
FROM table1
GROUP BY pnum, branch, year
)
, cte2 AS (
SELECT pnum2 AS pnum, branch, year, SUM(paid tax) AS total2
FROM table2
GROUP BY pnum2, branch, year
)
SELECT cte1.pnum, cte1.branch, cte1.year, cte1.total1, cte2.total2
FROM cte1
JOIN cte2
ON cte1.pnum = cte2.pnum
AND cte1.branch = cte2.branch
AND cte1.year = cte2.year
;
Окончательный исправленный результат:
pnum branch year total1 total2
5678 efgh 2018 4579 4579
Тестовый пример, предоставленный @EdmCoff, был полезен. Я скорректировал это, чтобы показать ваше сообщенное поведение, а затем добавил решение в нижней части скрипки.
Рабочий тестовый пример, отредактированный из комментария @EdmCoff
Обратите внимание, что @EdmCoff создал начальный тестовый набор, который позволил избежать проблемы, вручную агрегировав данные таблицы перед вставкой. Это, по сути, то, что мы делали в окончательном решении, в каждом термине CTE.
Ответ №2:
Вместо объединения используйте UNION ALL для получения всех строк 2 таблиц, а затем агрегируйте:
SELECT pnum, branch, year,
SUM(total) total,
SUM(total1) total1
FROM(
SELECT pnum, branch, year, total, 0 total1 FROM table1
UNION ALL
SELECT pnum2, branch, LEFT(year, 4), 0, paid tax FROM table2
) t
GROUP BY pnum, branch, year
Смотрите демонстрацию.