MySql新手,现有以下问题:
select column1,column2,column3 from TestTable ,现在要求将查询出来的column2赋值给变量count,因为column3存在多个值,无法使用select column1,column3,column3 into 变量1,变量2,变量3 from TestTable,会出现"Subquery returns more than 1 row"的错误。
//假设得到的结果
单据编号 日期 原单编号 金额 数量 商品名称
第一行: 1122 2012-3-2 111 20 1 衣服
第二行: 1122 2012-3-2 111 22 2 裤子
现在的需求是:输入单据编号,显示详细的信息,另外还要显示相关联的票据信息(即根据输入的单据编号查出原单编号然后将原单编号作为单据编号查找它的原单编号这样一直循环直到原单编号为空)
我的思路——部分代码:
create procedure chaxun(in piaoju varchar(20))
begin
declare ydbh varchar(20);//原单编号
declare haveYDBH int default 1;//是否有原单编号,默认有
set ydbh=piaoju;
while haveYDBH=1 do
select 某列1 as 别名1,某列2 as 别名2 ,某列3 as 别名3 into 某变量1,ydbh,某变量3 from 某表;
//实际中某列2,某列3等可能是子查询查到的结果
//如果不使用into 直接set ydbh=某列2或者别名2 会出现某列2或者别名2不存在的错误提示end while;
end
请问怎么解决?
select column1,column2,column3 from TestTable ,现在要求将查询出来的column2赋值给变量count,因为column3存在多个值,无法使用select column1,column3,column3 into 变量1,变量2,变量3 from TestTable,会出现"Subquery returns more than 1 row"的错误。
//假设得到的结果
单据编号 日期 原单编号 金额 数量 商品名称
第一行: 1122 2012-3-2 111 20 1 衣服
第二行: 1122 2012-3-2 111 22 2 裤子
现在的需求是:输入单据编号,显示详细的信息,另外还要显示相关联的票据信息(即根据输入的单据编号查出原单编号然后将原单编号作为单据编号查找它的原单编号这样一直循环直到原单编号为空)
我的思路——部分代码:
create procedure chaxun(in piaoju varchar(20))
begin
declare ydbh varchar(20);//原单编号
declare haveYDBH int default 1;//是否有原单编号,默认有
set ydbh=piaoju;
while haveYDBH=1 do
select 某列1 as 别名1,某列2 as 别名2 ,某列3 as 别名3 into 某变量1,ydbh,某变量3 from 某表;
//实际中某列2,某列3等可能是子查询查到的结果
//如果不使用into 直接set ydbh=某列2或者别名2 会出现某列2或者别名2不存在的错误提示end while;
end
请问怎么解决?
解决方案 »
- 用一条SQL语句 查询出每门课都大于80分的学生姓名
- 杯具了,latin1编码的字段写入中文怎么恢复?
- temptable类型的视图问题
- 还原数据库时出现问题Unknown MySQL server
- MySql数据查询速度问题
- eclipse C++ 如何连接mysql???
- 请教高人帮小弟看下,修改一句sql
- 如何使自己增加的用户有建库的权限?
- 使用用navicat连接mysql时r提示连接名localhost已存在(或已在其它navicat产品),请指定其定连接名
- 继续提问,关于查询规定时间未交易用户(异常用户)
- 请问使用C语言通过ODBC链接mysql在非VC6的环境中该怎么做?
- 知道一个字段的一个数据,怎么查询到这个数据在哪个表里
begin
declare dj varchar(30);
declare r datetime;
declare th varchar(30);
declare td datetime;
declare sp1 varchar(30);
declare sp2 varchar(30);
declare s varchar(10);
declare j varchar(10);
declare bz varchar(30);
declare z varchar(10);
declare hhd varchar(30);
declare hhr datetime;-- 需要定义接收游标数据的变量
declare ydbh varchar(30);
-- 游标
declare cur CURSOR for select ydjh from ipos_qtlsd;
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
set cur=xiaopiao;
-- 开始循环
read_loop: LOOP
-- 提取游标里的数据,这里只有一个,多个的话也一样;
FETCH cur INTO ydbh;
-- 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件
select c.djbh into dj from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select ( from_unixtime(c.rq)) into r from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select c.ydjh into th from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select (from_unixtime((select e.rq from ipos_qtlsd e where e.djbh=c.ydjh)) ) into td from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select d.spdm into sp1 from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select d.spmc into sp2 from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select d.sl into s from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select d.je into j from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select d.bzj into bz from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select ( case when d.th_sl>0 then '已退货'when d.sp_status=12 then '已换货' when d.sl<0 then '退货' when d.sp_status=0 then '正常' when d.sp_status=1 then '促销' when d.sp_status=2 then '换购' when d.sp_status=7 then '生日' when d.sp_status=11 then '换货' end)
into z from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select (case when d.th_sl>0 then (select e.djbh from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=12 then (select e.djbh from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=11 then (select e.djbh from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=7 then (select e.djbh from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=2 then (select e.djbh from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=1 then (select e.djbh from ipos_qtlsd e where e.ydjh=ydbh)
when d.sl<0 then (select e.djbh from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=0 then '无退货单'
end ) into hhd from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
select (from_unixtime(
(case when d.th_sl>0 then (select e.rq from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=12 then (select e.rq from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=11 then (select e.rq from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=7 then (select e.rq from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=2 then (select e.rq from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=1 then (select e.rq from ipos_qtlsd e where e.ydjh=ydbh)
when d.sl<0 then (select e.rq from ipos_qtlsd e where e.ydjh=ydbh)
when d.sp_status=0 then '0000-00-00 00:00:00'
end ) )) into hhr from ipos_qtlsd c,ipos_qtlsdmx d where c.id=d.pid and c.id=(select id from ipos_qtlsd a where a.djbh=ydbh);
END LOOP;
-- 关闭游标
CLOSE cur;
end
出现133-Variable or condition declaration after cursor or handler declaration如果我将
DECLARE done INT DEFAULT FALSE;放在declare cur CURSOR for select ydjh from ipos_qtlsd;之前又会出现1193-Unknown system variable 'cur'