大神告诉我，mysql存储过程中的sql涉及到排序报错

qq_21012927 2017-12-14 06:42:18
delimiter //
CREATE PROCEDURE pk10_Chance()
BEGIN
DECLARE @flag varchar(50);DECLARE @num1 INT;DECLARE @num2 INT;DECLARE @num3 INT;
DECLARE @num4 INT; DECLARE @num5 INT; DECLARE @num6 INT;DECLARE @num7 INT;
DECLARE @num8 INT; DECLARE @num9 INT;DECLARE @num10 INT;DECLARE @num11 INT;DECLARE @num12 VARCHAR(50);
DECLARE @num13 INT;DECLARE @num14 INT;DECLARE @num15 INT;

DECLARE tempCursor CURSOR
FOR(
DECLARE @rownum=1;
select
'第1名大小统计' as flag,
sum(case when num=1 then 1 else 0 end) as sum1,
sum(case when num=2 then 1 else 0 end) as sum2,
sum(case when num=3 then 1 else 0 end) as sum3,
sum(case when num=4 then 1 else 0 end) as sum4,
sum(case when num=5 then 1 else 0 end) as sum5,
sum(case when num=6 then 1 else 0 end) as sum6,
sum(case when num=7 then 1 else 0 end) as sum7,
sum(case when num=8 then 1 else 0 end) as sum8,
sum(case when num=9 then 1 else 0 end) as sum9,
sum(case when num=10 then 1 else 0 end) as sum10,
sum(case when num=11 then 1 else 0 end) as sum11,
sum(case when num=12 then 1 else 0 end) as sum12,
sum(case when num=13 then 1 else 0 end) as sum13,
sum(case when num=14 then 1 else 0 end) as sum14,
sum(case when num=15 then 1 else 0 end) as sum15 from
(select case when num1 = num2 then @rownum:=@rownum+1 else @rownum:=1 end as num from (select num1,num,(select case when num1>5 then 1 else 0 end from pk10 where issuenum = t.num-1) as num2
from (
select case when num1>5 then 1 else 0 end as num1,issuenum as num from pk10 order by issue DESC limit 10000 ) t
)t2)t3

)
OPEN tempCursor;
FETCH NEXT FROM tempCursor INTO @flag,@num1,@num2,@num3,@num4,@num5,@num6,@num7,
@num8,@num9,@num10,@num11,@num12,@num13,@num14,@num15;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into pk10_C (flag,num1,num2,num3,num4,num5,num6,num7,num8,num9,num10
,num11,num12,num13,num14,num15
) values (@flag,@num1/10000,@num2/@num1,@num3/@num2,@num4/@num3,@num5/@num4,@num6/@num5,
@num7/@num6,@num8/@num7,@num9/@num8,@num10/@num9,@num11/@num10,@num12/@num11,@num13/@num12,
@num14/@num13,@num15/@num14);
FETCH NEXT FROM tempCursor INTO @flag,@num1,@num2,@num3,@num4,@num5,@num6,@num7,
@num8,@num9,@num10,@num11,@num12,@num13,@num14,@num15;
END
CLOSE tempCursor
DEALLOCATE tempCursor
END

...全文
145 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2017-12-15
  • 打赏
  • 举报
 回复 
CREATE PROCEDURE pk10_Chance()
BEGIN
DECLARE _flag varchar(50);DECLARE  _num1  INT;DECLARE   _num2 INT;DECLARE  _num3 INT; 
DECLARE _num4  INT;   DECLARE _num5 INT; DECLARE   _num6 INT;DECLARE    _num7 INT; 
DECLARE _num8 INT; DECLARE _num9 INT;DECLARE  _num10 INT;DECLARE  _num11 INT;DECLARE  _num12 VARCHAR(50);
DECLARE _num13 INT;DECLARE _num14 INT;DECLARE _num15 INT;

DECLARE _cur_finish boolean DEFAULT false;
DECLARE  tempCursor CURSOR 
FOR
select
 '第1名大小统计' as flag,
 sum(case when num=1 then 1 else 0 end)  as sum1,
 sum(case when num=2 then 1 else 0 end)  as sum2,
 sum(case when num=3 then 1 else 0 end)  as sum3,
 sum(case when num=4 then 1 else 0 end)  as sum4,
 sum(case when num=5 then 1 else 0 end)  as sum5,
 sum(case when num=6 then 1 else 0 end)  as sum6,
 sum(case when num=7 then 1 else 0 end)  as sum7,
 sum(case when num=8 then 1 else 0 end)  as sum8,
 sum(case when num=9 then 1 else 0 end)  as sum9,
 sum(case when num=10 then 1 else 0 end)  as sum10,
 sum(case when num=11 then 1 else 0 end)  as sum11,
 sum(case when num=12 then 1 else 0 end)  as sum12,
 sum(case when num=13 then 1 else 0 end)  as sum13,
 sum(case when num=14 then 1 else 0 end)  as sum14,
 sum(case when num=15 then 1 else 0 end)  as sum15 from 
(select case when num1 = num2 then @rownum:=@rownum+1 else @rownum:=1 end as num  
from (select num1,num,(select case when num1>5 then 1 else 0 end 
 from pk10 where issuenum = t.num-1) as num2
from (
select case when num1>5 then 1 else 0 end as num1,issuenum as num  from pk10  
order by issue DESC limit 10000 ) t
)t2)t3,(select @rownum:=1) _x
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cur_finish = true; 
OPEN  tempCursor; 
_cur1_loop: LOOP
	FETCH NEXT FROM  tempCursor INTO _flag,_num1,_num2,_num3,_num4,_num5,_num6,_num7,
   _num8,_num9,_num10,_num11,_num12,_num13,_num14,_num15; 
   IF _cur_finish THEN LEAVE _cur1_loop; END IF;

  insert into pk10_C (flag,num1,num2,num3,num4,num5,num6,num7,num8,num9,num10
   ,num11,num12,num13,num14,num15
) values (_flag,_num1/10000,_num2/_num1,_num3/_num2,_num4/_num3,_num5/_num4,_num6/_num5,
  _num7/_num6,_num8/_num7,_num9/_num8,_num10/_num9,_num11/_num10,_num12/_num11,_num13/_num12,
_num14/_num13,_num15/_num14); 
END LOOP;
  CLOSE tempCursor;
END
zjcxc 2017-12-15
  • 打赏
  • 举报
 回复
你这是 SQL Server 的存储过程吧？ 连这个都有 WHILE @@FETCH_STATUS = 0
qq_21012927 2017-12-15
  • 打赏
  • 举报
 回复
谢谢你的回答，我的mysql版本是5.7.17 我把@ 都去了，还是不行 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @rownum = 1; 提示我在set @rownum = 1 的时候报错
zjcxc 2017-12-15
  • 打赏
  • 举报
 回复
你用的什么版本？ 5。7 版这个就是不对的 DECLARE @flag varchar(50)
qq_21012927 2017-12-14
  • 打赏
  • 举报
 回复
set @rownum=1; select '第1名大小统计' as flag, sum(case when num=1 then 1 else 0 end) as sum1, sum(case when num=2 then 1 else 0 end) as sum2, sum(case when num=3 then 1 else 0 end) as sum3, sum(case when num=4 then 1 else 0 end) as sum4, sum(case when num=5 then 1 else 0 end) as sum5, sum(case when num=6 then 1 else 0 end) as sum6, sum(case when num=7 then 1 else 0 end) as sum7, sum(case when num=8 then 1 else 0 end) as sum8, sum(case when num=9 then 1 else 0 end) as sum9, sum(case when num=10 then 1 else 0 end) as sum10, sum(case when num=11 then 1 else 0 end) as sum11, sum(case when num=12 then 1 else 0 end) as sum12, sum(case when num=13 then 1 else 0 end) as sum13, sum(case when num=14 then 1 else 0 end) as sum14, sum(case when num=15 then 1 else 0 end) as sum15 from (select case when num1 = num2 then @rownum:=@rownum+1 else @rownum:=1 end as num from (select num1,num,(select case when num1>5 then 1 else 0 end from pk10 where issuenum = t.num-1) as num2 from ( select case when num1>5 then 1 else 0 end as num1,issuenum as num from pk10 order by issue DESC limit 10000 ) t )t2)t3 存储过程中涉及到排序，我这个sql执行是没问题的，放到存储过程里面就不行了
qq_21012927 2017-12-14
  • 打赏
  • 举报
 回复
delimiter // CREATE PROCEDURE pk10_Chance() BEGIN DECLARE @flag varchar(50);DECLARE @num1 INT;DECLARE @num2 INT;DECLARE @num3 INT; DECLARE @num4 INT; DECLARE @num5 INT; DECLARE @num6 INT;DECLARE @num7 INT; DECLARE @num8 INT; DECLARE @num9 INT;DECLARE @num10 INT;DECLARE @num11 INT;DECLARE @num12 VARCHAR(50); DECLARE @num13 INT;DECLARE @num14 INT;DECLARE @num15 INT; DECLARE tempCursor CURSOR FOR( DECLARE @rownum=1; select '第1名大小统计' as flag, sum(case when num=1 then 1 else 0 end) as sum1, sum(case when num=2 then 1 else 0 end) as sum2, sum(case when num=3 then 1 else 0 end) as sum3, sum(case when num=4 then 1 else 0 end) as sum4, sum(case when num=5 then 1 else 0 end) as sum5, sum(case when num=6 then 1 else 0 end) as sum6, sum(case when num=7 then 1 else 0 end) as sum7, sum(case when num=8 then 1 else 0 end) as sum8, sum(case when num=9 then 1 else 0 end) as sum9, sum(case when num=10 then 1 else 0 end) as sum10, sum(case when num=11 then 1 else 0 end) as sum11, sum(case when num=12 then 1 else 0 end) as sum12, sum(case when num=13 then 1 else 0 end) as sum13, sum(case when num=14 then 1 else 0 end) as sum14, sum(case when num=15 then 1 else 0 end) as sum15 from (select case when num1 = num2 then @rownum:=@rownum+1 else @rownum:=1 end as num from (select num1,num,(select case when num1>5 then 1 else 0 end from pk10 where issuenum = t.num-1) as num2 from ( select case when num1>5 then 1 else 0 end as num1,issuenum as num from pk10 order by issue DESC limit 10000 ) t )t2)t3 ) OPEN tempCursor; FETCH NEXT FROM tempCursor INTO @flag,@num1,@num2,@num3,@num4,@num5,@num6,@num7, @num8,@num9,@num10,@num11,@num12,@num13,@num14,@num15; WHILE @@FETCH_STATUS = 0 BEGIN insert into pk10_C (flag,num1,num2,num3,num4,num5,num6,num7,num8,num9,num10 ,num11,num12,num13,num14,num15 ) values (@flag,@num1/10000,@num2/@num1,@num3/@num2,@num4/@num3,@num5/@num4,@num6/@num5, @num7/@num6,@num8/@num7,@num9/@num8,@num10/@num9,@num11/@num10,@num12/@num11,@num13/@num12, @num14/@num13,@num15/@num14); FETCH NEXT FROM tempCursor INTO @flag,@num1,@num2,@num3,@num4,@num5,@num6,@num7, @num8,@num9,@num10,@num11,@num12,@num13,@num14,@num15; END CLOSE tempCursor DEALLOCATE tempCursor END
oracle入门到大神（备mysql、java基础、javaee必经之路）
Oracle10g的安装、orcale的基本概念介绍、命令行常用操作、Scott用户下的表结构介绍、sql简介、简单查询、限定查询、对结果集排序、单行函数、多表查询、子查询、数据库变更、事务处理、表的管理、约束、rownum、...
mysql in报错_MySQL WHERE IN（）?mysql
mysql的搜索结果问题mysql分页的sql语句，求解？？?String sql = "select s.* from user s where s.id limit ((?-1)*?),?";You have an error in your SQL syntax; ...爱吃鱼的程序员2020-08-21 15:24:230 浏览量...
Java+SQL+MySQL+Hive存储过程汇总
​ 在我们做一个复杂的项目时，会多次涉及到与数据库的连接，这时我们会一次次的来用SQL语句进行对数据库的连接，但如果我们这时用存储过程的话就只需要连接一次数据库就可以了，从而省去大量的SQL语句。 存储过程的...
MYSQL因为排序规则不同导致的问题的解决办法
[Err] 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='MYSQL因为排序规则不同导致的问题的解决办法
那些被人忽略的MySQL储存过程
可能在网上发布会出现代码排序错乱的问题...记录MYSQL存储过程中的关键语法： DELIMITER // 声明语句结束符，用于区分; CREATE PROCEDURE demo_in_parameter(IN p_in int) 声明存储过程 BEGIN .... END 存储...
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章