CTE结果在CTE CASE WHEN中不可见

Question

我在MySQL沙箱环境中工作，得到运行时错误："字段列表"中的未知列"tab.maxd"。这是我的原始代码：

with tab as(
select max(id) as maxid from seat)
select id,
CASE
WHEN (id % 2 <> 0 and id = tab.maxid) THEN student
WHEN (id % 2 <> 0 and id < tab.maxid) THEN lead(student) over(order by id)
ELSE lag(student) over(order by id)
END AS `student`
from seat;

我做错了什么？谢谢你的帮助！

Answer 1

试试这个：

;with tab as(
select max(id) as maxid from seat
)
select id,
CASE
WHEN (id % 2 <> 0 and id = (select maxid from tab)) THEN student
WHEN (id % 2 <> 0 and id < (select maxid from tab)) THEN lead(student) over(order by id)
ELSE lag(student) over(order by id)
END AS `student`
from seat;

另一种选择是：

select id,
CASE
WHEN (id % 2 <> 0 and id = (select max(id) from seat)) THEN student
WHEN (id % 2 <> 0 and id < (select max(id) from seat)) THEN lead(student) over(order by id)
ELSE lag(student) over(order by id)
END AS `student`
from seat;