mysql - 无法在 case when 语句中放入子查询条件

Question

我有一张 table ，座位，需要根据给定条件打印一些数据。由于从来没有描述性的语法错误，我无法执行查询，而且我无法推断其背后的原因。

这是我生成语法错误的查询:

select (case id when mod(id, 2)=0 then id=id+1 
                when mod(id,2) <> 0 and id != (select count(*) from seat) then id=id-1 
                else id=id) as id, 
       student 
from seat;

错误是:

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 ') as id, student from seat' at line 2

任务是切换相邻行的(特定列的)值。完整问题可用here .

Answer 1

您不需要在 CASE 表达式的值中指定输出变量。此外，当您在 WHEN 条件中使用逻辑表达式时，您不应指定 CASE 的输入，并且您缺少 END 到 案例。试试这个:

select case when mod(id, 2) =  0 then id-1 
            when mod(id, 2) <> 0 and id != (select count(*) from seat) then id+1
            else id
       end as id, 
       student 
from seat
order by id;

输出(leetcode 问题)

id  student
1   Doris
2   Abbot
3   Green
4   Emerson
5   Jeames

Demo on dbfiddle