626，换座位

换座位

分析

第一步：求出有多少学生

with t1 as (
    select count(*) cnt from Seat
)select
    *
from Seat, t1;

第二步：我们在实际开发中，一般不会对主键进行更改，所以我们添加新的一列即行号

with t1 as (
    select count(*) cnt from Seat
)select
    *,
    row_number() over (order by id) row_num
from Seat, t1;

第三步：开始进行对行号的判断，若行号为奇数且奇数行号与总人数不相等时，将行号加一，若行号为奇数且奇数行号与总人数相等时，行号不变，否则行号减一，在重新对行号排序即可

with t1 as (
    select count(*) cnt from Seat
), t2 as (
    select
        *,
        row_number() over (order by id) row_num
    from Seat, t1
)select
    case
        when mod(row_num, 2) != 0 and id != cnt then row_num + 1
        when  mod(row_num, 2) != 0 and id = cnt then row_num
        else row_num - 1
    end id,
    student
from t2 order by id;

实现

with t1 as (
    select count(*) cnt from Seat
), t2 as (
    select
        *,
        row_number() over (order by id) row_num
    from Seat, t1
)select
    case
        when mod(row_num, 2) != 0 and id != cnt then row_num + 1
        when  mod(row_num, 2) != 0 and id = cnt then row_num
        else row_num - 1
    end id,
    student
from t2 order by id;

总结

两个元素滑动窗口两两交换，最后判断一下边界