mysql if else begin_MySQL 存储过程例子,不能在if else里面用begin end否则会报错Error Code : 1064!...

Error Code : 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

报错是因为mysql的procedure里面if else语句里面, 用了begin end语句,去掉了就OK了.

DELIMITER $$

USE `sportgbmj`$$

DROP PROCEDURE IF EXISTS `sp_web_addmiddayrank`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_web_addmiddayrank`()

BEGIN

/*

修订记录：

-------------------------------------------------------------------

版本 修订人 修订日期 修订描述

-------------------------------------------------------------------

1.0.0 2013-06-15 查询当前中午12:30-13:30比赛排名

该sp 当前比赛结束后执行

排名先决条件：

1：在一场内必须完满10(含)局以上

-------------------------------------------------------------------

*/

DECLARE i_date DATE ;

DECLARE i_starttime TIME;

DECLARE i_endtime TIME;

SET i_date = NOW();

SET i_endtime = NOW();

IF (i_endtime > '22:30') THEN

BEGIN

/* 夜间赛场 */

SET i_starttime = '21:30';

SET i_endtime = '22:30';

END

ELSE

BEGIN

/* 中午场 */

SET i_starttime = '12:30';

SET i_endtime = '13:30';

END

END IF;

CREATE TEMPORARY TABLE tmp_rank(

id INT NOT NULL AUTO_INCREMENT -- 自增

,userid INT -- 用户标识

,beans INT -- bean 汇总

,rounds TINYINT -- 局数 汇总

,posttime TIME -- 时间 (该玩家本场比赛最后的时间)

) ENGINE = MYISAM;

INSERT INTO tmp_rank (userid,beans,rounds,posttime)

SELECT

userid

,SUM(CASE WHEN consume > 0 THEN consume ELSE 0 END) beans

,COUNT(userid) rounds

,MAX(posttime) AS posttime

FROM

score AS S

INNER JOIN

scorelist AS SL

ON

S.pid = SL.pid

WHERE

roomid IN ('1001') AND (postdate = i_date AND posttime >='12:30:00' AND posttime <='13:30:00')

GROUP BY

userid

HAVING

(rounds >= 10)

ORDER BY

beans DESC

,maxtime ASC

LIMIT 500;

/*

插入排名表

*/

INSERT INTO

hf_playranklist (userid,rank,beans,rounds,postdate,posttime)

SELECT

userid,beans,rounds,i_date,posttime

FROM tmp_rank;

/*

插入奖品表

*/

INSERT INTO hf_prizelist (userid,STATUS,prizeid,receivetime,postdate)

SELECT

userid

,0

,(CASE WHEN id >= 11 THEN 4

WHEN (id >= 3 AND id <= 10) THEN 3

WHEN id = 2 THEN 2

WHEN id = 1 THEN 1

END) AS prizeid

,beans

,rounds

,posttime

FROM

tmp_rank AS R

WHERE

rank >= 30;

END$$

DELIMITER ;

Mysql报错Error Code&colon; 1175&period; You are using safe update

使用MySQL执行update的时候报错:Error Code: 1175. You are using safe update mode and you tried to update a tabl ...

CentOS命令登录MySQL时，报错ERROR 1045 &lpar;28000&rpar;&colon;

CentOS命令登录MySQL时,报错ERROR 1045 (28000): Access denied for user root@localhost (using password: NO)错误解 ...

MySQL报错ERROR 1558 &lpar;HY000&rpar;&colon; Column count of mysql&period;user is wrong&period;

MySQL报错ERROR 1558 (HY000): Column count of mysql.user is wrong. 1.今天在使用MySQL创建数据库时出现如下报错: mysql> ...

解决MySQL报错ERROR 2002 &lpar;HY000&rpar;【转】

今天在为新的业务线搭架数据库后,在启动的时候报错 root@qsbilldatahis-db01:/usr/local/mysql/bin# ./mysql ERROR 2002 (HY000): C ...

MySQL查询报错 ERROR&colon; No query specified

今天1网友,查询报错ERROR: No query specified,随后它发来截图. root case:查询语法错误 \G后面不能再加分号;,由于\G在功能上等同于;,假设加了分号,那么就是;; ...

随机推荐

【FLUENT案例】04：利用DDPM&plus;DEM模拟鼓泡流化床

1 引言2 问题描述3 准备4 FLUENT前处理 1 引言 DEM碰撞模型扩展了DPM模型的功能,能够用于稠密颗粒流动的模拟.该模型可以与DDPM(Dense DPM)模型何用以模拟颗粒对主相的阻碍 ...

【转】css布局居中和CSS内容居中区别和对应DIV CSS代码