SQL-запрос, чтобы получить транзакцию для всех пользователей, имеющих определенную метку времени
У меня есть структура таблицы sql следующим образом:
id INT(11),
amount DOUBLE(10,2),
createdAt DATETIME,
user_id INT(11),
tx_status INT(11).
значение tx_status может быть 1 (кредит), 2 (дебет) и 3 (расчет). Теперь то, что я хочу, это все последние транзакции всех пользователей, где транзакция createdAt больше, чем последняя createdAt, когда tx_status = 3 для конкретного пользователя.
Данные показаны ниже:
id . amount . createdAt . user_id . tx_status
112 500 . 2019-05-15 00:30:40 345 . 1
113 . 300 2019-05-16 06:45:29 345 . 2
116 . 600 . 2019-05-16 06:45:33 345 . 3
119 . 567 . 2019-05-17 07:56:55 345 . 1
121 500 . 2019-05-18 00:30:40 346 . 1
123 . 300 2019-05-19 06:45:29 346 . 2
124 . 600 . 2019-05-20 06:45:33 346 . 3
126 . 567 . 2019-05-21 07:56:55 346 . 1
Вывод должен быть:
id . amount . createdAt . user_id . tx_status
119 . 567 . 2019-05-17 07:56:55 345 . 1
126 . 567 . 2019-05-21 07:56:55 346 . 1
да, и я хочу эти конкретные дебеты и кредиты после последнего расчета для каждого пользователя.
Ответы 1
Этот запрос даст вам желаемые результаты. Он использует производную таблицу максимального времени расчетов, используя условную агрегацию, чтобы гарантировать, что, если расчеты не произошли, это время будет «1970-01-01» (так что будут возвращены все записи).
SELECT t1.*
FROM transactions t1
JOIN (SELECT user_id, MAX(CASE WHEN tx_status = 3 THEN createdAt ELSE '1970-01-01' END) AS last_settlement
FROM transactions t2
GROUP BY user_id) t2 ON t2.user_id = t1.user_id AND t2.last_settlement < t1.createdAt
Вывод (для ваших образцов данных)
id amount createdAt user_id tx_status
119 567 2019-05-17 07:56:55 345 1
126 567 2019-05-21 07:56:55 346 1
Что делать, если транзакции со статусом = 3 нет?
затем показать все транзакции для этого конкретного пользователя
В вашем образце данных есть несколько неверных моментов времени, но почему бы вам не включить обе транзакции, которые произошли после расчета для пользователя 345?
Существует только одна транзакция с идентификатором 119 для идентификатора пользователя 345
id 113 имеет недопустимое время, которое больше, чем то, которое имеет tx_status=3. Возможно, вы могли бы исправить эти времена в примере данных
@MrugeshThaker Я обновил свой ответ в соответствии с вашими новыми данными и требованием вернуть все записи для пользователя без расчетов.
Вы хотите увидеть, есть ли какие-либо дебеты или кредиты после последнего расчета?