2 g2008ghjk g2008ghjk 于 2013.10.24 12:19 提问

mysql 存储过程 在线急求解答!!!

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'
就没有问题,程序能正常执行。
这是为什么???

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!