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币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
急急急!求解答!
编写一个能对任意类型(整型,字符型,浮点型)数据进行排序(由小到大)的通用函数,并用main()测试该函数  可以帮忙解决这个题目吗?新手,谢谢啦!
急求解答!!!!!!!!!!!!!!
有没有会xml和asp.net的高手啊!! 有没有高手会把form表单的内容提交到一个XML文件中啊??? 最好能把截图发过来!!谢谢了!!!
一个计算阶乘的存储过程
一个计算阶乘的存储过程,使用递归算法实现
存储过程性能优化
近期为某项目组写存储过程进行一些较为复杂的计算。存储过程,在性能上,主要是节省了编译的时间,除此而外,似乎并没有什么优势。相反,它语法比较弱,写起来不够方便,甚至对面向过程编程也支持得很不好。所以在写这些存储过程的时候,我一度想写成CLR存储过程。我写的这些存储过程,是根据别人给出的算法(伪码)来写的,里面有两个嵌套的循环,共计80万次循环。循环里面有计算、查找各种参数表。最后将结果保存到结果表。
从零开始写MySql存储过程(四)通过存储过程实现阶乘的计算
   首先,我认为在实际的开发中,应该没人会用存储过程去计算阶乘,但是用这个作为练习我觉得还是挺不错的,因为通过这个存储过程的编写可以熟悉参数类型,存储过程内部使用自定义变量,循环结构的语法的使用mysql&amp;gt; create procedure jiecheng(in parameter int) -&amp;gt; begin -&amp;gt; declare var int; -...
Mysql存储过程调试工具
Mysql存储过程调试工具,可以方便的调试MYSQL的存储过程。
python3实现最大子序列和的在线处理方法
python3实现最大子序列和的在线处理方法 学习内容上传,便于复习和分享。 实现方法:使用在线处理的解题方法,这个方法的特点是从头至尾依次加上序列中的元素,每次加和后判断当前和(代码中的this_sum)是否大于零,若大于零继续累加,否则当前和置零并继续累加。运算过程中同时选择出最大的当前和作为输出结果。 此方法只用了一次循环,时间复杂度为n。
MySql的存储过程练习
#创建带参数的自定义函数 CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) returns FLOAT(8,2) return (num1+num2)/2; #调用函数 select f2(10,15); #创建不带参数的自定义函数 create function f1() RETURNS VARCHAR(30) re
javabean求解旧错
javabean+jsp问题,求纠错。急/
mysql存储过程参数比较
mysql存储过程参数比较