BEGIN

  DECLARE sid INT(10) DEFAULT  0;

  DECLARE money INT(10) DEFAULT  0;

  DECLARE row_count INT;

  DECLARE done INT DEFAULT FALSE;#默认游标读出有记录

  DECLARE cur_order CURSOR  for

    SELECT m.id,sum(o.price) money from ims_bj_shopn_order as o LEFT JOIN ims_bj_shopn_member as m on o.uid=m.id where o.status in ('1','2','3','6','7') and (m.isadmin=1 or m.id=m.staffid) and o.createtime BETWEEN 1514736000 and 1546271999  GROUP BY o.uid ORDER BY money desc;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 绑定控制变量到游标,游标循环结束自动转true

  #select cur_order;

    open cur_order;-- 打开游标

    myLoop:LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到

        FETCH cur_order INTO sid,money; -- 将游标当前读取行的数据顺序赋予自定义变量

        IF done THEN -- 判断是否继续循环

                    LEAVE myLoop; -- 结束循环

                END IF;

                -- 自己要做的事情,在 sql 中直接使用自定义变量即可

                SELECT count(*) INTO row_count from think_purchase_amount where sellerid=sid;

                IF (row_count=0) THEN

                    INSERT INTO think_purchase_amount(sellerid,count,year) VALUES(sid,money,2018);

        　　　　　ELSE

                    UPDATE think_purchase_amount  SET  count=count+money,year=2018 WHERE sellerid=sid;

                END IF;

    END LOOP myLoop;-- 结束自定义循环体

  CLOSE cur_order;-- 关闭游标

END