• Table A has fields RefuelId and RefuelTime. Records the time I refuel my car.
• Table B has fields Price and UpdateTime. Records the price and the time when the gasoline price is updated.
• Result wanted: A.RefuelId, A.RefuelTime, B.Price, B.UpdateTime.

B.Update is the closet time before RefuelTime. Have tried left join, but there is no common field between Table A and B. Return Cartesian product.

Table A, RefuelRecord:

Table B, GasUpdateRecord :

Result table:

SQL:

Create table RefuelRecord (RefuelId int, RefuelTime datetime);
INSERT INTO RefuelRecord VALUES(1, '2022-02-01 12:15:00');
INSERT INTO RefuelRecord VALUES(2, '2022-03-01 12:15:00');
INSERT INTO RefuelRecord VALUES(3, '2022-04-01 12:15:00');

Create table PriceUpdateRecord (UpdateTime datetime, Price double);
INSERT INTO PriceUpdateRecord VALUES('2022-01-20 12:15:00', 9.0);
INSERT INTO PriceUpdateRecord VALUES('2022-02-20 12:15:00', 8.0);
INSERT INTO PriceUpdateRecord VALUES('2022-03-20 12:15:00', 7.0);

CodePudding user response：

Do a LEFT join of RefuelRecord to PriceUpdateRecord on the condition that UpdateTime should be less than RefuelTime.
Then use aggregation and SQLite's feature of bare columns to get for each RefuelId the row from PriceUpdateRecord with the max UpdateTime:

SELECT r.RefuelId, 
       r.RefuelTime,
       MAX(p.UpdateTime) UpdateTime,
       p.Price
FROM RefuelRecord r LEFT JOIN PriceUpdateRecord p
ON p.UpdateTime < r.RefuelTime
GROUP BY RefuelId;

See the demo.