get total value based on different unit price,quantity at different time, MySQL

Language: Layout:

drop table if exists t;

create table t (id int, transaction_time date, item char(1), unit_price float, quantity int, subtotal float);

insert into t values
(1,      '2012-5-15',          'A',         1.00,         15,         15.00),
(2,      '2012-5-15',          'A',         3.00,         15,         45.00),
(3,      '2012-5-15',          'B',         1.00,         10,         10.00),
(4,      '2012-6-10',          'A',         2.00,         15,         30.00),
(5,      '2012-6-15',          'A',         2.00,         10,         20.00);

SELECT transaction_time, 
       'A' AS item,
       SUM(quantity) AS quantity,
       SUM(quantity*unit_price) AS subtotal
FROM (           
    SELECT transaction_time, unit_price,
           (SELECT quantity
            FROM t
            WHERE t.item = 'A' 
                  AND t.unit_price = up.unit_price
                  AND t.transaction_time <= times.transaction_time
            ORDER BY transaction_time DESC LIMIT 1) AS quantity
    FROM (
       SELECT DISTINCT transaction_time
       FROM t
       WHERE item = 'A') AS times
    CROSS JOIN (
       SELECT DISTINCT unit_price
       FROM t
       WHERE item = 'A') AS up) AS x
GROUP BY transaction_time


absolute service time: 0,39 sec

get total value based on different unit price,quantity at different ti...