問題描述

1.問題描述：我想要從一個(gè)表中查找數(shù)據(jù)，然后取其中一些字段批量存到另一張表中，剛學(xué)習(xí)用存儲(chǔ)過程，但是一直顯示影響0行，不知道是哪里錯(cuò)了，請(qǐng)各位幫幫忙。謝謝了！2.代碼：

BEGIN #Routine body goes here... DECLARE id INT DEFAULT 0; DECLARE user_Id INT DEFAULT 0; DECLARE course_id INT DEFAULT 0; DECLARE playercount INT DEFAULT 0; DECLARE course_name VARCHAR(255); DECLARE play_time INT DEFAULT 0; DECLARE finish INT DEFAULT 0; DECLARE _done TINYINT(1) DEFAULT 0; DECLARE mian_cur CURSOR FOR SELECT user_id,course_id,SUM(playercount) AS playercount,SUM(play_time) AS play_time,course_name FROM `edu_course_studyhistory` WHERE user_id = userId GROUP BY course_id ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1; OPEN mian_cur; loop_xxx:LOOP FETCH FROM mian_cur INTO user_id,course_id,playercount,play_time,course_name;IF _done=1 THEN LEAVE loop_xxx; END IF;INSERT INTO edu_course_history VALUES(NULL,user_id,course_id,playercount,course_name,now(),play_time,0); END LOOP;END

3.錯(cuò)誤信息

問題解答

從這個(gè)edu_course_studyhistory這個(gè)表取數(shù)據(jù)插入edu_course_history這個(gè)表么？1.先確認(rèn)下有沒有數(shù)據(jù)：

SELECT user_id, course_id, SUM(playercount) AS playercount, SUM(play_time) AS play_time, course_nameFROM `edu_course_studyhistory`WHERE user_id = userIdGROUP BY course_id;

2.試試執(zhí)行能成功么？

INSERT INTO edu_course_historyVALUES (NULL,user_id,course_id,playercount,course_name,now(),play_time,0 );回答2：

看起來沒必要用存儲(chǔ)過程吧？維護(hù)起來還麻煩。

INSERT INTO table1(field1, field2, ...)SELECT field1, field2 FROM table2WHERE ...