Блог

Предположим, что таблица transaction_record содержит следующие 10 записей:

 transaction_id,transaction_date,transaction_type,transaction_amount
123,03.11.2020,credit,20
123,04.11.2020,credit,20
123,03.11.2020,credit,30
123,04.11.2020,credit,30
123,03.11.2020,debit,20
123,04.11.2020,debit,20
123,03.11.2020,debit,30
123,04.11.2020,debit,30
456,03.11.2020,credit,100 #Should be shown in query
456,03.11.2020,debit,100  #Should be shown in query
  

Каждый transaction_id должен дважды отображаться в таблице на основе значений transaction_type credit и debit. Из-за ошибки transaction_id=123 были ошибочно размещены для двух дат: 03.11.2020 и 04.11.2020.
Однако Transaction_id=456 является правильным.
Как я могу получить только эти две строки, представляющие transaction_id=456, используя SQL, учитывая, что я заранее не знаю, какие транзакции являются «хорошими»? Я знаю, какая ошибка произошла при transaction_date = ‘03.11.2020’ и ‘04.11.2020’.
Мой диалект — sqlite, но я был бы признателен за ответ на любом SQL, чтобы понять концепцию. Спасибо.

Вы можете получить transaction_id нужные вам s, если вы group by transaction_id и установите условия в HAVING предложении:

 select transaction_id 
from transaction_record
group by transaction_id
having count(*) = 2 and count(distinct transaction_date) = 1 and count(distinct transaction_type) = 2
  

Используйте приведенный выше запрос с оператором IN для выбора полных строк из таблицы:

 select * 
from transaction_record
where transaction_id in (
  select transaction_id 
  from transaction_record
  group by transaction_id
  having count(*) = 2 and count(distinct transaction_date) = 1 and count(distinct transaction_type) = 2
)
  

Или, если вы уверены, что только правильные transaction_id s имеют только 1 дату, вы можете использовать NOT EXISTS :

 select t.* 
from transaction_record t
where not exists (
  select 1 from transaction_record
  where transaction_id = t.transaction_id and transaction_date <> t.transaction_date
) 
  

Или, если вы уверены, что только правильные transaction_id s имеют точные 2 строки, используйте COUNT() функцию window:

 select transaction_id, transaction_date, transaction_type, transaction_amount
from (
  select *, count(*) over (partition by transaction_id) counter
  from transaction_record
)
where counter = 2
  

Посмотрите демонстрацию.
Результаты:

 > transaction_id | transaction_date | transaction_type | transaction_amount
> -------------: | :--------------- | :--------------- | -----------------:
>            456 | 03.11.2020       | credit           |                100
>            456 | 03.11.2020       | debit            |                100