此查询中有什么问题？我收到错误"select is not valid at this position for this server version, expecting : '(', WIT

Question

我的查询

错误：对于此服务器版本，select 在此位置无效，期望："("，WITH

set @prev="SAME";
select `date`, `COL1` , `COL2` ,
if( `COL1`>`COL2` and ( (@prev="SAME") or (@prev="UP") ) ) then
"DOWN", @prev:="DOWN"
else if( `COL1` < `COL2` and ( (@prev="SAME") or (@prev="DOWN") ) ) then
"UP", @prev:="UP"
else
"SAME"
END IF
as 'sign' 
from 
temp;

Answer 1

不能在查询中使用IF...THEN。您可以使用IF(condition, truevalue, falsevalue)或CASE WHEN condition THEN value1 WHEN condition2 THEN value2 ELSE something END。

IF...THEN是过程语法。

Answer 2

以下是答案，它奏效了：

set @prev="SAME";
select date, COL1 , COL2 , 
CASE 
WHEN( COL1>COL2 and ( (@prev="SAME") or (@prev="UP") ) )
THEN 
@prev:="DOWN"
WHEN( COL1 < COL2 and ( (@prev="SAME") or (@prev="DOWN") ) )
THEN
@prev:="UP" 
ELSE "SAME"
END as 'sign' 
from
temp;