mysql 存儲(chǔ)過(guò)程

要求是取出前七周中每一周的總額（只能通過(guò)一周最后一天的數(shù)據(jù)減去該周第一天的數(shù)據(jù)得到），
使用了以下的存儲(chǔ)過(guò)程

BEGIN
    DECLARE i int;
    DECLARE theDate1 varchar(20);
    DECLARE theDate2 varchar(20);
    DECLARE theFirstTotalPower double;
    DECLARE theLastTotalPower double;

    SET i = 1;
    SET theDate1 = DATE_FORMAT(DATE_SUB(curdate()-WEEKDAY(curdate()), INTERVAL 6 WEEK), "%Y-%m-%d");
    SET theDate2 = DATE_FORMAT(DATE_SUB(curdate()-WEEKDAY(curdate()) + 6, INTERVAL 6 WEEK), "%Y-%m-%d");
    SET theFirstTotalPower = 0;
    SET theLastTotalPower = 0;

    DROP TABLE IF EXISTS tb_temp1;
    CREATE TEMPORARY TABLE tb_temp1(
        `tmp_Id` int(11)  NOT NULL AUTO_INCREMENT primary key, 
        `totalpower` double,
        `thetime` int
    ) ENGINE = MYISAM DEFAULT charset = utf8; 

    WHILE (i < 8) DO
        SET theFirstTotalPower = (SELECT `data`.total_power FROM `data` WHERE date = theDate1 ORDER BY `data`.time ASC LIMIT 1);
        SET theLastTotalPower = (SELECT `data`.total_power FROM `data` WHERE date = theDate2 ORDER BY `data`.time DESC LIMIT 1);
        SET i = i + 1;
        SET theDate1 = DATE_FORMAT(DATE_SUB(curdate()-WEEKDAY(curdate()), INTERVAL 7-i WEEK), "%Y-%m-%d");
        SET theDate2 = DATE_FORMAT(DATE_SUB(curdate()-WEEKDAY(curdate()) + 6, INTERVAL 7-i WEEK), "%Y-%m-%d");

        INSERT INTO tb_temp1 VALUES(0, FORMAT(theLastTotalPower - theFirstTotalPower, 2), week(theDate1, 1)-1);
    END WHILE;
        
    SELECT * FROM tb_temp1;
    drop table tb_temp1;
END

得到的結(jié)果如下：