http://sqlfiddle.com/#!2/20108/1
这是完成工作的存储过程
CREATE PROCEDURE myProc()BEGIN -- Drop and create the temp table DROP TABLE IF EXISTS reached; CREATE TABLE reached ( sku CHAr(32) PRIMARY KEY, record_date date, nb int, total int) ENGINE=HEAP;-- Initial insert, the starting point is the MAX sales record_date of each productINSERT INTO reached SELECT products.sku, max(sales.record_date), 0, 0FROM productsjoin sales on sales.sku = products.skugroup by products.sku;-- loop until there is no more updated rowsiterloop: LOOP -- Update the temptable with the values of the date - 1 row if found update reached join sales on sales.sku=reached.sku and sales.record_date=reached.record_date set reached.record_date = reached.record_date - INTERVAL 1 day, reached.nb=reached.nb+1, reached.total=reached.total + sales.items; -- If no more rows are updated it means we hit the most longest days_sold IF ROW_COUNT() = 0 THEN LEAVE iterloop; END IF;END LOOP iterloop;-- select the results of the temp tableSELECT products.sku, products.title, products.price, reached.total as sales, reached.nb as days_sold from reachedjoin products on products.sku=reached.sku;END//
那你只需要做
call myProc()
