Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS a;
- CREATE TABLE IF NOT EXISTS a(
- id INT AUTO_INCREMENT PRIMARY KEY,
- trans_date DATE,
- currency CHAR(3)
- );
- INSERT INTO a(trans_date,currency)
- VALUES
- ('2015-02-01','USD'),
- ('2015-04-04','USD'),
- ('2017-04-04','USD'),
- ('2015-01-01','JPY'),
- ('2015-04-04','JPY'),
- ('2017-04-04','JPY');
- SELECT * FROM a;
- +----+------------+----------+
- | id | trans_date | currency |
- +----+------------+----------+
- | 1 | 2015-02-01 | USD |
- | 2 | 2015-04-04 | USD |
- | 3 | 2017-04-04 | USD |
- | 4 | 2015-01-01 | JPY |
- | 5 | 2015-04-04 | JPY |
- | 6 | 2017-04-04 | JPY |
- +----+------------+----------+
- DROP TABLE IF EXISTS b;
- CREATE TABLE IF NOT EXISTS b(
- currency CHAR(3),
- last_update DATE,
- price FLOAT
- );
- INSERT INTO b(currency,last_update,price)
- VALUES
- ('USD','2015-01-01',13200.00),
- ('USD','2015-03-01',13160.00),
- ('USD','2017-04-04',13340.00),
- ('JPY','2015-01-01',110.00),
- ('JPY','2015-03-01',108.00),
- ('JPY','2017-04-04',112.00);
- SELECT * FROM b;
- +----------+-------------+-------+
- | currency | last_update | price |
- +----------+-------------+-------+
- | USD | 2015-01-01 | 13200 |
- | USD | 2015-03-01 | 13160 |
- | USD | 2017-04-04 | 13340 |
- | JPY | 2015-01-01 | 110 |
- | JPY | 2015-03-01 | 108 |
- | JPY | 2017-04-04 | 112 |
- +----------+-------------+-------+
- SELECT a.id,a.trans_date,MAX(b.last_update) AS last_update, a.currency
- FROM a
- JOIN b ON (a.trans_date>=b.last_update AND a.currency=b.currency)
- GROUP BY a.id;
- +----+------------+-------------+----------+
- | id | trans_date | last_update | currency |
- +----+------------+-------------+----------+
- | 1 | 2015-02-01 | 2015-01-01 | USD |
- | 2 | 2015-04-04 | 2015-03-01 | USD |
- | 3 | 2017-04-04 | 2017-04-04 | USD |
- | 4 | 2015-01-01 | 2015-01-01 | JPY |
- | 5 | 2015-04-04 | 2015-03-01 | JPY |
- | 6 | 2017-04-04 | 2017-04-04 | JPY |
- +----+------------+-------------+----------+
- SELECT a.id,a.trans_date,b.last_update, a.currency, b.price
- FROM a
- JOIN
- (
- SELECT a.id,MAX(b.last_update) AS last_update,b.currency
- FROM a
- JOIN b ON (a.trans_date>=b.last_update AND a.currency=b.currency)
- GROUP BY a.id
- ) c USING(id)
- JOIN b ON (b.last_update=c.last_update AND b.currency=c.currency);
- +----+------------+-------------+----------+-------+
- | id | trans_date | last_update | currency | price |
- +----+------------+-------------+----------+-------+
- | 1 | 2015-02-01 | 2015-01-01 | USD | 13200 |
- | 2 | 2015-04-04 | 2015-03-01 | USD | 13160 |
- | 3 | 2017-04-04 | 2017-04-04 | USD | 13340 |
- | 4 | 2015-01-01 | 2015-01-01 | JPY | 110 |
- | 5 | 2015-04-04 | 2015-03-01 | JPY | 108 |
- | 6 | 2017-04-04 | 2017-04-04 | JPY | 112 |
- +----+------------+-------------+----------+-------+
Add Comment
Please, Sign In to add comment