SQL练习00011

需求:一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的，改变相邻俩学生的座位。
示例：

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

假如数据输入的是上表，则输出结果如下：

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

--建表语句
Create table If Not Exists seat(id int, student varchar(255))
Truncate table seat
insert into seat (id, student) values ('1', 'Abbot')
insert into seat (id, student) values ('2', 'Doris')
insert into seat (id, student) values ('3', 'Emerson')
insert into seat (id, student) values ('4', 'Green')
insert into seat (id, student) values ('5', 'Jeames')

--答案1
--窗口函数
SELECT
  id,
  CASE
    when id%2=1 then lead1 else lag1
  end as student
FROM
  (
  select
    id,
    student,
    lead(student,1,student) over() as lead1,
    lag(student,1) over() as lag1
  from
  seat
  ) 
  a

--答案2
--直接使用case when
SELECT
  if(id>(select count(1) from seat),id-1,id) as id,
  student
from
  (
  SELECT
    case
      when mod(id,2)=1 then id+1 else id-1
    end as id,
    student
  from
    seat
  ) 
  a order by 
  id

--答案3
--join后case when
select
  s1.id as id,
  case 
    when mod(s1.id,2)=1 and s2.student is not null then s2.student 
    when mod(s1.id,2)=0 then s3.student
    else s1.student
  end as student
from
  seat s1 left join 
  seat s2 on 
  s1.id=s2.id-1 left JOIN 
  seat s3 on 
  s3.id+1=s1.id