Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Сбросить таблицы
- TRUNCATE TABLE transactions;
- TRUNCATE TABLE currency;
- -- Наполнить таблицы тестовыми записями
- INSERT INTO transactions (id, date, client_id, currency, amount) VALUES (123, '2018-05-23', 23, 'EURO', 259);
- INSERT INTO transactions (id, date, client_id, currency, amount) VALUES (124, '2018-05-25', 23, 'EURO', 114);
- INSERT INTO transactions (id, date, client_id, currency, amount) VALUES (125, '2018-05-26', 23, 'EURO', 532);
- INSERT INTO currency (id, date, currency, value) VALUES (12, '2018-05-23', 'EURO', 69.5);
- INSERT INTO currency (id, date, currency, value) VALUES (13, '2018-05-26', 'EURO', 68.5);
- -- Сумма всех транзакций выбранного клиента (client_id = 23). Если дата отсутствует в таблице currency, то берется ближайшая предыдущая дата.
- SELECT SUM(amount * value)
- FROM transactions, currency
- WHERE transactions.client_id = 23 and currency.date = (
- SELECT date FROM currency WHERE date <= transactions.date ORDER BY date DESC LIMIT 1
- );
- -- Результат: 62365.5
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement