mysql 存储过程 在线 急急急 求解答！！！
这是我写的存储过程，
set onepickUpVehicle = '京AG7614'的时候就会报错
ERROR 1242 (21000): Subquery returns more than 1 row
但是当set onepickUpVehicle = '京AD4977' 就没有问题，程序能正常执行。这是为什么？？？
BEGIN
/*起始时间|上车时间*/
DECLARE startime varchar(20);
/*结束时间|下车时间*/
DECLARE endtime varchar(20) ;
/*上车站号*/
DECLARE upnum int(11) ;
/*循环判断条件*/
DECLARE b varchar(10);
/*避免死循环，设置循环次数*/
DECLARE i int(11) ;
/*当前站号*/
DECLARE starnum int(11);
/*下一站站号*/
DECLARE endnum int(11);
/*最大站号*/
DECLARE maxnum int(11);
/*判断站号顺序*/
DECLARE checknum varchar(20);
/*统计人数*/
DECLARE peopels int(11);
/*上车人数*/
DECLARE uppeople int(11);
/*下车人数*/
DECLARE getpeople int(11);
/*临时变量*/
DECLARE num int(11);
DECLARE num1 int(11);
DECLARE num2 int(11);
DECLARE num3 int(11);
DECLARE drivernum int(11) ;
DECLARE drivermax int(11) ;
DECLARE driverone varchar(20) ;
DECLARE uptime varchar(20) ;
DECLARE gettime varchar(20) ;
DECLARE onepickUpVehicle varchar(20) ;
DECLARE onecarUpnum int(11);
DECLARE onetransactionType varchar(20);
DECLARE onetransactionDate varchar(20);
set names utf8;
set onepickUpVehicle = '京AG7614';
set onetransactionType = '一般消费';
set onetransactionDate = '20131015';
CREATE TEMPORARY TABLE IF NOT EXISTS carpeople (
pickUpVehicle varchar(20) not null,
pickUpTime varchar(20) not null,
pickUpStationNum int(11) not null,
peoplenum int(11) not null
);
CREATE TEMPORARY TABLE IF NOT EXISTS carerror (
pickUpVehicle varchar(20) not null,
pickUpTime varchar(20) not null,
pickUpStationNum int(11) not null,
peoplenum int(11) not null
);
CREATE TEMPORARY TABLE IF NOT EXISTS onetime (
id int(11) not null AUTO_INCREMENT,
pickUpTime varchar(20) not null,
gettime varchar(20) not null,
lorr varchar(20) not null,
PRIMARY KEY (`id`)
);
CREATE TEMPORARY TABLE IF NOT EXISTS oneday (
driver varchar(8) not null,
pickUpTime varchar(20) not null,
pickUpVehicle varchar(20) not null,
pickUpStationNum int(11) not null,
getOffStationNum int(11) not null ,
revenueAmount float(5) not null
);
CREATE TEMPORARY TABLE IF NOT EXISTS onedriver (
driver varchar(20)
);
CREATE TEMPORARY TABLE IF NOT EXISTS onecarUp(
driver varchar(8) not null,
pickUpTime varchar(20) not null,
pickUpVehicle varchar(20) not null,
pickUpStationNum int(11) not null,
getOffStationNum int(11) not null,
revenueAmount float(5) not null
);
CREATE TEMPORARY TABLE IF NOT EXISTS people(
id int(11) not null ,
pickUpTime varchar(20) not null,
getOffTime varchar(20) not null,
starnum int(11) not null
);
insert into oneday (
select pgtraffic.card.driver,pgtraffic.card.pickUpTime,pgtraffic.card.pickUpVehicle,pgtraffic.card.pickUpStationNum,pgtraffic.card.getOffStationNum,pgtraffic.card.revenueAmount
from pgtraffic.card
where pgtraffic.card.transactionDate='20131015'
order by pgtraffic.card.pickUpTime
);
insert into onecarUp (
select o.driver,o.pickUpTime,o.pickUpVehicle,o.pickUpStationNum,o.getOffStationNum,o.revenueAmount
from oneday as o
where o.pickUpVehicle=onepickUpVehicle
);
insert into onedriver (
select o.driver
from onecarUp as o
group by o.driver
);
set drivermax = (select count(o.driver) from onedriver as o);
set drivernum = 1;
WHILE drivernum <= drivermax DO
set driverone = (select o.driver from onedriver as o limit 0,1);
set onecarUpnum = (
select count(o.getOffStationNum)
from onecarUp as o
where o.revenueAmount != 1
and o.driver = driverone
and o.pickUpStationNum != o.getOffStationNum
);
if onecarUpnum is not null then
/*获取最大站号*/
set maxnum = (select o.pickUpStationNum from onecarUp as o order by o.pickUpStationNum desc limit 0,1);
set num = (select o.getOffStationNum from onecarUp as o order by o.getOffStationNum desc limit 0,1);
if num > maxnum then
set maxnum = num;
end if;
set startime = (select o.pickUpTime from onecarUp as o limit 0,1);
set b = 't';
set i = 1;
WHILE b = 't' DO
CREATE TEMPORARY TABLE IF NOT EXISTS onecarNum(
pickUpTime varchar(20) not null,
pickUpVehicle varchar(20) not null,
pickUpStationNum int(11) not null,
getOffStationNum int(11) not null
);
insert into onecarNum (
select o.pickUpTime,o.pickUpVehicle,o.pickUpStationNum,o.getOffStationNum from onecarUp as o where o.pickUpTime >= startime
);
set upnum = (select o.pickUpStationNum from onecarNum as o limit 0,1);
set endtime = (select o.pickUpTime from onecarNum as o where o.pickUpStationNum != upnum limit 0,1);
set num = (select count(o.pickUpStationNum) from onecarNum as o where o.pickUpTime = endtime and o.pickUpStationNum = upnum);
if num is not null and num > 0 then
set num1 = (select count(o.pickUpStationNum) from onecarNum as o where o.pickUpTime = endtime and o.pickUpStationNum = upnum group by o.pickUpStationNum order by o.pickUpStationNum limit 0,1);
set num2 = (select count(o.pickUpStationNum) from onecarNum as o where o.pickUpTime = endtime and o.pickUpStationNum != upnum group by o.pickUpStationNum order by o.pickUpStationNum desc limit 0,1);
if num1 >= num2 then
set endtime = (select o.pickUpTime from onecarNum as o where o.pickUpStationNum != upnum and o.pickUpTime > endtime limit 0,1);
end if;
end if;
if endtime is null then
set endtime = (select ou.pickUpTime from onecarUp as ou order by ou.pickUpTime desc limit 0,1);
set b = 'f';
insert into people (
select i,startime,endtime,o.pickUpStationNum from
onecarNum as o
group by o.pickUpStationNum
);
end if;
if endtime is not null then
set num1 = (select o.getOffStationNum from onecarNum as o where o.pickUpTime < endtime and o.pickUpTime >= startime order by o.getOffStationNum limit 0,1);
set num2 = (select o.getOffStationNum from onecarNum as o where o.pickUpTime < endtime and o.pickUpTime >= startime order by o.getOffStationNum desc limit 0,1);
if (upnum > num1 and upnum > num2) or (upnum < num1 and upnum < num2) then
insert into people (
select i,startime,endtime,o.pickUpStationNum from
onecarNum as o
where o.pickUpTime < endtime
and o.pickUpTime >= startime
and o.pickUpStationNum = upnum
group by o.pickUpStationNum
);
end if;
if !((upnum > num1 and upnum > num2) or (upnum < num1 and upnum < num2)) then
CREATE TEMPORARY TABLE IF NOT EXISTS tem (
id int(1) not null AUTO_INCREMENT,
pickUpTime varchar(20) ,
pickUpVehicle varchar(20) ,
pickUpStationNum int(11) ,
getOffStationNum int(11) ,
PRIMARY KEY (`id`)
);
insert into tem (select null,o.pickUpTime,o.pickUpVehicle,o.pickUpStationNum,o.getOffStationNum from onecarNum as o where o.pickUpTime < endtime and o.pickUpTime >= startime and o.pickUpStationNum = upnum);
set num1 = (select t.id from tem as t order by t.id desc limit 0,1);
set num2 = 1;
set checknum = null;
WHILE num2 <= num1 DO
set num3 = (select t.getOffStationNum from tem as t where t.id = num2 order by t.pickUpTime );
if checknum is null then
if num3 < upnum then
set checknum = 'l';
end if;
if num3 > upnum then
set checknum = 'r';
end if;
end if;
if checknum is not null then
if num3 < upnum then
if checknum != 'l' then
set uptime = (select t.pickUpTime from tem as t where t.id = num2 order by t.pickUpTime );
insert into people (
select i,startime,uptime,o.pickUpStationNum from
onecarNum as o
where o.pickUpTime < uptime
and o.pickUpTime >= startime
and o.pickUpStationNum = upnum
group by o.pickUpStationNum
);
set startime = uptime;
end if;
end if;
if num3 > upnum then
if checknum != 'r' then
set uptime = (select t.pickUpTime from tem as t where t.id = num2 order by t.pickUpTime );
insert into people (
select i,startime,uptime,o.pickUpStationNum from
onecarNum as o
where o.pickUpTime < uptime
and o.pickUpTime >= startime
and o.pickUpStationNum = upnum
group by o.pickUpStationNum
);
set startime = uptime;
end if;
end if;
end if;
END WHILE;
insert into people (
select i,startime,endtime,o.pickUpStationNum from
onecarNum as o
where o.pickUpTime < endtime
and o.pickUpTime >= startime
and o.pickUpStationNum = upnum
group by o.pickUpStationNum
);
DROP TABLE tem;
end if;
end if;
if i = 100 then
insert into carerror (pickUpVehicle, pickUpTime, pickUpStationNum, peoplenum) values ( onepickUpVehicle , '数据有问题',0,0);
select * from carerror;
set b = 'f';
end if;
set upnum = null;
set startime = endtime;
set endtime = '';
DROP TABLE onecarNum;
set i = i + 1;
END WHILE;
set i = 1;
/*
select num;
select maxnum;
select * from onecarUp;
select * from people;
select * from onetime;
select * from oneday as o where o.pickUpVehicle='京AK9706';
*/
end if;
delete from onedriver where onedriver.driver = driverone;
set drivernum = drivernum + 1;
END WHILE;
select * from carpeople;
DROP TABLE onecarUp;
DROP TABLE oneday;
DROP TABLE people;
DROP TABLE onetime;
DROP TABLE carpeople;
DROP TABLE carerror;
DROP TABLE onedriver;
END
mysql存储过程-1242X1? mysql 存储过程，1242，
[解决办法]
具体那部分出错你能定位不？我这里没有环境
