SQL: выберите транзакции, строки которых не соответствуют критериям внутри одной и той же таблицы
У меня есть таблица с транзакциями:
Transactions
------------
id | account | type | date_time | amount
----------------------------------------------------
1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000
2 | 003 | 'R' | '2012-01-02 12:53:10' | 1500
3 | 003 | 'A' | '2012-01-03 13:10:01' | -1500
4 | 002 | 'R' | '2012-01-03 17:56:00' | 2000
5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000
6 | 002 | 'A' | '2012-01-04 13:23:01' | -2000
7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000
8 | 003 | 'R' | '2012-01-05 12:12:00' | 1250
9 | 003 | 'A' | '2012-01-06 17:24:01' | -1250
, и я хочу выбрать все определенного типа (' R '), но не те, которые немедленно (в порядке поля date_time) иметь другую транзакцию другого типа ('A') для той же учетной записи ...
Итак, запрос должен выдать следующие строки, учитывая предыдущий пример:
id | account |type | date | amount
----------------------------------------------------
1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000
5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000
7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000
(Как вы можете видеть, строка 2 не ' t отображается, потому что строка 3 "отменяет" его ... также строка 4 "отменяется" строкой 6 '; строка 7 действительно появляется (даже если учетная запись 003 принадлежит отмененной строке # 2, на этот раз в строке 7 она не отменена любая строка 'A'); И строка 8 не появится (это тоже для учетной записи 003, так как теперь эта отменяется на 9, что тоже не отменяет 7, только предыдущая: 8 ...
I пробовали соединения, подзапросы в предложениях Where, но я действительно не знаю, как мне сделать свой запрос ...
Что я пробовал:
Попытка объединиться:
SELECT trans.type as type,
trans.amount as amount,
trans.date_time as dt,
trans.account as acct,
FROM Transactions trans
INNER JOIN ( SELECT t.type AS type, t.acct AS acct, t.date_time AS date_time
FROM Transactions t
WHERE t.date_time > trans.date_time
ORDER BY t.date_time DESC
) AS nextTrans
ON nextTrans.acct = trans.acct
WHERE trans.type IN ('R')
AND nextTrans.type NOT IN ('A')
ORDER BY DATE(trans.date_time) ASC
Это вызывает ошибку, так как я могу 'не вводить внешние значения для JOIN в MySQL.
Попытка выполнить подзапрос в where:
SELECT trans.type as type,
trans.amount as amount,
trans.date_time as dt,
trans.account as acct,
FROM Transactions trans
WHERE trans.type IN ('R')
AND trans.datetime <
( SELECT t.date_time AS date_time
FROM Transactions t
WHERE t.account = trans.account
ORDER BY t.date_time DESC
) AS nextTrans
ON nextTrans.acct = trans.acct
ORDER BY DATE(trans.date_time) ASC
Это неверно , Я могу ввести внешние значения в WHERE в MySQL, но мне не удается найти способ правильно отфильтровать то, что мне нужно ...
ВАЖНОЕ РЕДАКТИРОВАНИЕ:
Мне удалось найти решение, но теперь оно нуждается в серьезной оптимизации. Вот оно:
SELECT *
FROM (SELECT t1.*, tFlagged.id AS cancId, tFlagged.type AS cancFlag
FROM transactions t1
LEFT JOIN (SELECT t2.*
FROM transactions t2
ORDER BY t2.date_time ASC ) tFlagged
ON (t1.account=tFlagged.account
AND
t1.date_time < tFlagged.date_time)
WHERE t1.type = 'R'
GROUP BY t1.id) tCanc
WHERE tCanc.cancFlag IS NULL
OR tCanc.cancFlag <> 'A'
Я присоединился к таблице сам с собой, просто учитывая ту же учетную запись и отличное date_time.Присоединение выполняется по дате_времени. Группировка по идентификатору. Мне удалось получить только первый результат соединения, который является следующей транзакцией для той же учетной записи.
Затем при внешнем выборе я отфильтровываю те, у которых есть «А», поскольку это означает, что следующая транзакция была фактически отменой для нее. Другими словами, если для той же учетной записи нет следующей транзакции или если следующей транзакцией является 'R', то она не отменяется и должна отображаться в результате ...
Я получил следующее:
+----+---------+------+---------------------+--------+--------+----------+
| id | account | type | date_time | amount | cancId | cancFlag |
+----+---------+------+---------------------+--------+--------+----------+
| 1 | 001 | R | 2012-01-01 10:01:00 | 1000 | 5 | R |
| 5 | 001 | R | 2012-01-04 12:30:01 | 1000 | NULL | NULL |
| 7 | 003 | R | 2012-01-04 15:13:10 | 3000 | 8 | R |
+----+---------+------+---------------------+--------+--------+----------+
Он связывает каждую транзакцию со следующей по времени для той же учетной записи, а затем отфильтровывает те, которые были отменены ... Успех !!
Как я уже сказал, проблема сейчас в оптимизации. Мои реальные данные содержат много строк (как ожидается, таблица, содержащая транзакции во времени), и для таблицы из ~ 10 000 строк прямо сейчас я получил положительный результат с этим запросом за 1 минуту 44 секунды. Я полагаю, что дело в соединениях ... (Для тех, кто знает протокол здесь, что мне делать? Задайте здесь новый вопрос и опубликуйте его как решение этого? Или просто дождитесь дополнительных ответов здесь?)