window function 「lag」and 「lead」in sql

lag-lead.sql

select
    id,
    if(id%2=0, 
      lag(student, 1) over(order by id), -- 如果id是偶数，取上一行「lag」
      lead(student, 1, student) over(order by id) -- 如果id是奇数，取下一行，没有下一行时取自身「lead(field, 1, default)」
    ) as student
from 
    seat