坚持信仰的石头
2018-10-09 03:38
采纳率: 100%
浏览 2.2k

mysql数据库存储过程报错Error Code: 1172. Result consisted of more than one row

本人新手,在mysql数据库写了一个存储过程,第一遍执行正常,第二遍执行就报错:Error Code: 1172. Result consisted of more than one row。存储过程如下:
CREATE DEFINER=root@localhost PROCEDURE generatePage(in sid varchar(3),in snum int,in mnum int,in jnum int,in setnum int)
BEGIN

declare i int;
declare gid varchar(10);
declare title1 text;
declare answer1 text;
declare title2 text;
declare answer2 text;
declare title3 text;
declare answer3 text;
set i= 0;
insert into generateinfo(subjectid,singlenum,multiplenum,judgmentnum,setnum,createtime) values(sid,snum,mnum,jnum,setnum,now());
select @@IDENTITY into gid from generateinfo;

while i<setnum Do
    set title1 = "";
    set answer1 = "";
    set title2 = "";
    set answer2 = "";
    set title3 = "";
    set answer3 = "";

    if(snum>0) then
        SELECT GROUP_CONCAT(a.id separator ','),GROUP_CONCAT(a.answer separator ',') into title1,answer1 FROM (SELECT q.* FROM questionbank q where q.qsubject = sid and q.qtype = '1' ORDER BY rand() LIMIT snum) as a limit 1;
    end if;
    if(mnum>0) then
        SELECT GROUP_CONCAT(a.id separator ','),GROUP_CONCAT(a.answer separator ',') into title2,answer2 FROM (SELECT q.* FROM questionbank q where q.qsubject = sid and q.qtype = '2' ORDER BY rand() LIMIT mnum) as b limit 1;
    end if;
    if(jnum>0) then
        SELECT GROUP_CONCAT(a.id separator ','),GROUP_CONCAT(a.answer separator ',') into title3,answer3 FROM (SELECT q.* FROM questionbank q where q.qsubject = sid and q.qtype = '3' ORDER BY rand() LIMIT jnum) as c limit 1;
    end if;
    if(title1!=""&&answer1!="") then
        insert into pageinfo(generateid,questionid,questiontype,qanswer,setno) values(gid,title1,'1',answer1,i+1);
    end if;
    if(title2!=""&&answer2!="") then
        insert into pageinfo(generateid,questionid,questiontype,qanswer,setno) values(gid,title2,'2',answer2,i+1);
    end if;
    if(title3!=""&&answer3!="") then
        insert into pageinfo(generateid,questionid,questiontype,qanswer,setno) values(gid,title3,'3',answer3,i+1);
    end if;
    set i=i+1;
end while;

commit;
END
在网上查了很多资料,都是说需要在insert into 语句后面加上limit 1,但是我加上之后还是会报错,并且之前查询出来的结果是拼接数据,应该就只有一条。
求大神帮忙解决,谢谢!

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • 创业的鱼 2018-10-09 05:53
    已采纳

    我没细看,但建议你查一下查询语句或者用到结果集的地方吧,报错显然是结果集大于1啊

    打赏 评论

相关推荐 更多相似问题