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 num = (select count(p.starnum) from people as p);
set i = 1;
set checknum = null;
set startime = (select p.pickUpTime from people as p where p.id = i );
WHILE i < num DO
set starnum = (select p.starnum from people as p where p.id = i );
set endnum = (select p.starnum from people as p where p.id = (i + 1) );
if starnum > endnum then
if checknum is null then
set checknum = 'r';
end if;
if checknum != 'r' then
set uptime = (select p.pickUpTime from people as p where p.id = i );
set gettime = (select p.getOffTime from people as p where p.id = i );
if uptime = gettime then
set upnum = (select o.getOffStationNum from onecarUp as o where o.pickUpTime = uptime limit 0,1);
end if;
if uptime != gettime then
set upnum = (select o.getOffStationNum from onecarUp as o where o.pickUpTime >= uptime and o.pickUpTime < gettime limit 0,1);
end if;
if upnum > starnum then
set endtime = (select p.getOffTime from people as p where p.id = i );
insert into onetime(pickUpTime, gettime, lorr) values (startime, endtime, checknum );
set startime = endtime;
end if;
if upnum < starnum then
set endtime = (select p.pickUpTime from people as p where p.id = i );
insert into onetime(pickUpTime, gettime, lorr) values (startime, endtime, checknum );
set startime = endtime;
end if;
set checknum = null;
end if;
end if;
if starnum < endnum then
if checknum is null then
set checknum = 'l';
end if;
if checknum != 'l' then
set uptime = (select p.pickUpTime from people as p where p.id = i );
set gettime = (select p.getOffTime from people as p where p.id = i );
if uptime = gettime then
set upnum = (select o.getOffStationNum from onecarUp as o where o.pickUpTime = uptime limit 0,1);
end if;
if uptime != gettime then
set upnum = (select o.getOffStationNum from onecarUp as o where o.pickUpTime >= uptime and o.pickUpTime < gettime limit 0,1);
end if;
if upnum > starnum then
set endtime = (select p.pickUpTime from people as p where p.id = i );
insert into onetime(pickUpTime, gettime, lorr) values (startime, endtime, checknum );
set startime = endtime;
end if;
if upnum < starnum then
set endtime = (select p.getOffTime from people as p where p.id = i );
insert into onetime(pickUpTime, gettime, lorr) values (startime, endtime, checknum );
set startime = endtime;
end if;
set checknum = null;
end if;
end if;
if starnum = endnum then
set endtime = (select p.getOffTime from people as p where p.id = i );
insert into onetime(pickUpTime, gettime, lorr) values (startime, endtime, checknum );
set startime = (select p.pickUpTime from people as p where p.id = (i + 1) );
set checknum = null;
end if;
set i = i + 1;
END WHILE;
set endtime = (select p.getOffTime from people as p where p.id = i );
insert into onetime (pickUpTime, gettime, lorr) values (startime, endtime, checknum );
set num = (select o.id from onetime as o order by o.id desc limit 0,1);
set num1 = (select count(*) from onetime as o group by o.lorr order by o.lorr limit 0,1);
set num2 = (select count(*) from onetime as o group by o.lorr order by o.lorr desc limit 0,1);
if num1 is not null and num1 > 0 and num2 is not null and num2 > 0 then
if num1 - num2 > 1 or num1 - num2 < -1 then
insert into carerror (pickUpVehicle, pickUpTime, pickUpStationNum, peoplenum) values ( onepickUpVehicle , '数据有问题',0,0);
select * from carerror;
end if;
end if;
set i = 1;
WHILE i <= num DO
CREATE TEMPORARY TABLE IF NOT EXISTS upcar(
starnum int(11) not null,
peoplenum int(11) not null
);
CREATE TEMPORARY TABLE IF NOT EXISTS getcar(
starnum int(11) not null,
peoplenum int(11) not null
);
set startime = (select o.pickUpTime from onetime as o where o.id = i );
set endtime = (select o.gettime from onetime as o where o.id = i );
if startime = endtime then
insert into upcar(
select o.pickUpStationNum,count(o.pickUpTime) from
onecarUp as o
where o.pickUpTime = startime
group by o.pickUpStationNum
);
insert into getcar(
select o.getOffStationNum,count(o.pickUpTime) from
onecarUp as o
where o.pickUpTime = startime
group by o.getOffStationNum
);
end if;
if startime != endtime then
if i = num then
insert into upcar(
select o.pickUpStationNum,count(o.pickUpTime) from
onecarUp as o
where o.pickUpTime <= endtime
and o.pickUpTime >= startime
group by o.pickUpStationNum
);
insert into getcar(
select o.getOffStationNum,count(o.pickUpTime) from
onecarUp as o
where o.pickUpTime <= endtime
and o.pickUpTime >= startime
group by o.getOffStationNum
);
end if;
if i != num then
insert into upcar(
select o.pickUpStationNum,count(o.pickUpTime) from
onecarUp as o
where o.pickUpTime < endtime
and o.pickUpTime >= startime
group by o.pickUpStationNum
);
insert into getcar(
select o.getOffStationNum,count(o.pickUpTime) from
onecarUp as o
where o.pickUpTime < endtime
and o.pickUpTime >= startime
group by o.getOffStationNum
);
end if;
end if;
set peopels = 0;
set b = (select o.lorr from onetime as o where o.id = i );
if b = 'l' then
set num1 = 1;
WHILE num1 <= maxnum DO
set uppeople = (select u.peoplenum from upcar as u where u.starnum = num1);
set getpeople = (select u.peoplenum from getcar as u where u.starnum = num1);
if uppeople is null then
set uppeople = 0;
end if;
if getpeople is null then
set getpeople = 0;
end if;
set peopels = peopels + uppeople - getpeople;
insert into carpeople (pickUpVehicle, pickUpTime, pickUpStationNum, peoplenum) values ( onepickUpVehicle,onetransactionDate,num1,peopels);
set num1 = num1 + 1;
END WHILE;
end if;
if b = 'r' then
set num1 = maxnum;
WHILE num1 >= 1 DO
set uppeople = (select u.peoplenum from upcar as u where u.starnum = num1);
set getpeople = (select u.peoplenum from getcar as u where u.starnum = num1);
if uppeople is null then
set uppeople = 0;
end if;
if getpeople is null then
set getpeople = 0;
end if;
set peopels = peopels + uppeople - getpeople;
insert into carpeople (pickUpVehicle, pickUpTime, pickUpStationNum, peoplenum) values ( onepickUpVehicle,onetransactionDate,num1,peopels);
set num1 = num1 - 1;
END WHILE;
end if;
DROP TABLE upcar;
DROP TABLE getcar;
set i = i + 1;
END WHILE;
/*
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
这是我写的存储过程，
set onepickUpVehicle = '京AG7614'
的时候就会报错
ERROR 1242 (21000): Subquery returns more than 1 row
但是当
set onepickUpVehicle = '京AD4977'
就没有问题，程序能正常执行。
这是为什么？？？
