方法1:case when else end 逻辑条件
错误版本:
SELECT CASE
WHEN MOD(ID, 2) != 0
AND ID != COUNTS THEN ID = ID + 1
WHEN MOD(ID, 2) != 0
AND ID = COUNTS THEN ID = ID
ELSE ID = ID - 1
END AS ID, STUDENT
FROM SEAT, (
SELECT COUNT(1) AS COUNTS
FROM SEAT
) TMP
ORDER BY ID ASC;
对case when else end 语法理解不透彻 将then表达式写成了赋值表达式,赋值表达式返回值是0.then后面指定返回值即可,不要做赋值,正确答案:
SELECT CASE
WHEN MOD(ID, 2) != 0
AND ID != COUNTS THEN ID + 1
WHEN MOD(ID, 2) != 0
AND ID = COUNTS THEN ID
ELSE ID - 1
END AS ID, STUDENT
FROM SEAT, (
SELECT COUNT(1) AS COUNTS
FROM SEAT
) TMP
ORDER BY ID ASC;