SQL练习笔记二：换座位

-- 创建seat表
CREATE TABLE seat(
  id int(4) not NULL,
  student VARCHAR(10) not NULL
);

-- 插入数据
INSERT INTO seat VALUE(1,'Abbot');
INSERT INTO seat VALUE(2,'Doris');
INSERT INTO seat VALUE(3,'Emerson');
INSERT INTO seat VALUE(4,'Green');
INSERT INTO seat VALUE(5,'Jeames');

-- 解法1：使用UNION凭借查询结果
SELECT
  * 
FROM
  (-- id为偶数则id-1 --
  SELECT
    id - 1 AS id,
    student 
  FROM
    seat 
  WHERE
    id % 2 = 0 UNION ALL
  SELECT-- id为非最大奇数则id加1 --
    id + 1 AS id,
    student 
  FROM
    seat 
  WHERE
    id % 2 <> 0 
    AND id <(
    SELECT
      max( id ) 
    FROM
      seat 
    ) UNION ALL
  SELECT -- 查找最后一个id，且为奇数 --
    * 
  FROM
    seat 
  WHERE
    id =(
    SELECT
      max( id ) 
    FROM
      seat 
    ) 
    AND id % 2 <> 0 
  ) AS temp 
ORDER BY
  id;

-- 解法2：使用CASE条件分支判断查询
SELECT
  (
  CASE 
      WHEN id%2= 1 AND id != ( SELECT MAX( id ) FROM seat ) THEN id + 1 
      WHEN id%2= 0 THEN  id - 1 
      ELSE id 
      END 
  ) AS id,
  student 
FROM
  seat 
ORDER BY
  id;