游标嵌套if判断 为什么执行时会报出这个错误
Error Code: 1241. Operand should contain 1 column(s)代码如下
/*性别名称*/
declare sexName varchar(50);
/*性别ID*/
declare sexID int;
declare b int;
/*创建游标*/
declare cur_1 CURSOR FOR
SELECT CDSubID,CDDefinition FROM `mpacs`.`vsysdic_sex`;
/*当b = 1跳出循环*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
/*如果表存在则删除表*/
DROP TABLE IF EXISTS `tmp_tables`;
CREATE TEMPORARY TABLE `tmp_tables` (
sexName VARCHAR(50) NOT NULL,
counts int NOT NULL);
/* 打开游标 */
OPEN cur_1;
/* 循环执行 */
REPEAT
FETCH cur_1 INTO sexID,sexName;
if (begintime = '0001-01-01') and (endtime = '0001-01-01')
then
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID ;
commit ;
elseif (begintime != '0001-01-01') and (endtime = '0001-01-01')
then
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID and mpacs.tdatstudy.StudyDate > begintime;
commit ;
elseif (begintime = '0001-01-01') and (endtime != '0001-01-01')
then
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID and mpacs.tdatstudy.StudyDate < endtime;
commit ;
else
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID and mpacs.tdatstudy.StudyDate between begintime and endtime;
commit ;
end if;
UNTIL b
END REPEAT;
close cur_1;
Error Code: 1241. Operand should contain 1 column(s)代码如下
/*性别名称*/
declare sexName varchar(50);
/*性别ID*/
declare sexID int;
declare b int;
/*创建游标*/
declare cur_1 CURSOR FOR
SELECT CDSubID,CDDefinition FROM `mpacs`.`vsysdic_sex`;
/*当b = 1跳出循环*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
/*如果表存在则删除表*/
DROP TABLE IF EXISTS `tmp_tables`;
CREATE TEMPORARY TABLE `tmp_tables` (
sexName VARCHAR(50) NOT NULL,
counts int NOT NULL);
/* 打开游标 */
OPEN cur_1;
/* 循环执行 */
REPEAT
FETCH cur_1 INTO sexID,sexName;
if (begintime = '0001-01-01') and (endtime = '0001-01-01')
then
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID ;
commit ;
elseif (begintime != '0001-01-01') and (endtime = '0001-01-01')
then
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID and mpacs.tdatstudy.StudyDate > begintime;
commit ;
elseif (begintime = '0001-01-01') and (endtime != '0001-01-01')
then
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID and mpacs.tdatstudy.StudyDate < endtime;
commit ;
else
insert into `tmp_tables` select sexName, count(*) from `mpacs`.`tdatpatient`
inner join mpacs.tdatstudy on mpacs.tdatstudy.PID = `mpacs`.`tdatpatient`.PID
where sex = sexID and mpacs.tdatstudy.StudyDate between begintime and endtime;
commit ;
end if;
UNTIL b
END REPEAT;
close cur_1;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货