Блог

Главная - Вопросы по программированию - Поиск противоположных значений в таблице, часть 2

Поиск противоположных значений в таблице, часть 2

#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 Гарантирует, что учетная запись одинакова.


Метки: ,