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 product
INSERT INTO reached
SELECT products.sku, max(sales.record_date), 0, 0
FROM products
join sales on sales.sku = products.sku
group by products.sku;
-- loop until there is no more updated rows
iterloop: 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 table
SELECT products.sku, products.title, products.price, reached.total as sales, reached.nb as days_sold
from reached
join products on products.sku=reached.sku;
END//
那你只需要做
call myProc()
602392714
清零编程群