#sql #sql-server #excel
#sql #sql-сервер #excel
Вопрос:
У меня есть следующие исходные данные, см. http://sqlfiddle.com /#!18/9eecb/103607 с соответствующим кодом, который получает данные для сопоставления всех одинаковых сумм (абсолютных значений) с одним и тем же кодом счета.
Теперь я хочу получить вывод таким образом, чтобы он включал только пары элементов.
Например, в таблице ниже для кода учетной записи 1 у нас есть 3 значения, которые равны -10. Я бы хотел, чтобы код включал только 2 из них, игнорируя третье, так что значение 1 значение 2 = 0, гарантируя, что код учетной записи тот же.
Я не могу представить, как это может быть возможно, если я не включу оператор типа
T1.[Amount] T2[Amount] = 0
Но, похоже, это не работает
Текущий код SQL
SELECT *
FROM CTE T1
WHERE EXISTS
( SELECT * FROM CTE AS T2
WHERE t1.[Account Code] = T2.[Account Code]
AND t1.Amount = -t2.Amount
)
ORDER BY [Account Code], ABS(Amount)
Ответ №1:
Проблема, с которой вы сталкиваетесь, заключается в том, что у вас повторяющиеся суммы. Итак, все 10
s соответствуют всем -10
s — даже если их не одинаковое число.
Решение состоит в том, чтобы перечислить строки для каждой суммы, используя ROW_NUMBER()
. Затем вы можете выровнять совпадения.
Вы можете сделать это с помощью оконных функций (без EXISTS
):
SELECT t.*
FROM (SELECT t.*,
COUNT(*) OVER (PARTITION BY [Account Code], ABS(Amount), seqnum) as matches
FROM (SELECT cte.*,
ROW_NUMBER() OVER (PARTITION BY [Account Code], Amount ORDER BY (SELECT NULL)) as seqnum
FROM cte
) t
) t
WHERE matches = 2
ORDER BY [Account Code], Amount;
Вот скрипка db<> .
Самый внутренний подзапрос перечисляет суммы для каждой учетной записи.
Затем средний подзапрос перечисляет абсолютное значение сумм для каждого порядкового номера. Поскольку значения могут быть только положительными или отрицательными — и дубликатов нет из-за ROW_NUMBER()
— значение 2
указывает на совпадение.
Комментарии:
1. я не совсем понимаю, почему это работает? не могли бы вы уточнить логику? это просто, что, как только скрипт идентифицирует совпадение (т. Е. Где совпадение = 2 строки), он больше не рассматривает эту начальную строку. итак, в вашем примере он перестает искать вторичное совпадение для 10
2. могу ли я спросить, как это гарантирует, что критерии, по которым код учетной записи должен быть одинаковым? в моем приведенном выше снипе коды учетных записей указаны правильно, однако, если бы это было не так, как бы мы обеспечили, чтобы это оставалось ключевым критерием
3. @Новичок . , , Вопрос объясняет, как это работает.
PARTITION BY
Гарантирует, что учетная запись одинакова.