Advertisement
nheelyolis

sql_client_transactions_sum

Jan 31st, 2023 (edited)
1,720
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.13 KB | Source Code | 0 0
  1. -- Сбросить таблицы
  2. TRUNCATE TABLE transactions;
  3. TRUNCATE TABLE currency;
  4. -- Наполнить таблицы тестовыми записями
  5. INSERT INTO transactions (id, date, client_id, currency, amount) VALUES (123, '2018-05-23', 23, 'EURO', 259);
  6. INSERT INTO transactions (id, date, client_id, currency, amount) VALUES (124, '2018-05-25', 23, 'EURO', 114);
  7. INSERT INTO transactions (id, date, client_id, currency, amount) VALUES (125, '2018-05-26', 23, 'EURO', 532);
  8. INSERT INTO currency (id, date, currency, value) VALUES (12, '2018-05-23', 'EURO', 69.5);
  9. INSERT INTO currency (id, date, currency, value) VALUES (13, '2018-05-26', 'EURO', 68.5);
  10.  
  11. -- Сумма всех транзакций выбранного клиента (client_id = 23). Если дата отсутствует в таблице currency, то берется ближайшая предыдущая дата.
  12. SELECT SUM(amount * value)
  13. FROM transactions, currency
  14. WHERE transactions.client_id = 23 and currency.date = (
  15.     SELECT date FROM currency WHERE date <= transactions.date ORDER BY date DESC LIMIT 1
  16. );
  17.  
  18. -- Результат: 62365.5
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement