又回到SQL了喔，先卡位。

大大每周一挑戰又來了

下面是我的解法
邏輯:
1.假如mod 0代表id要減1
2.假如mod 1代表id要加1
3.假如id是最大值的時候，要判斷mod是不是1，假如是不用修改id

set @maxid = (select max(id) maxid from seat);
select 
  case 
    when (T1.`id` % 2 = 0 ) then T1.`id` -1
    when ((T1.`id` % 2 = 0 ) and not (@maxid = T1.`id` ))  then T1.`id` +1
    else T1.`id`
  end `id`
  ,T1.`student`
from seat  T1
order by `id`;

LeetCode不能在前面宣告變數所以改成cross join

select 
  case 
    when (T1.`id` % 2 = 0 ) then T1.`id` -1
    when ((T1.`id` % 2 = 1) and not (@maxid = T1.`id` ))  then T1.`id` +1
    else T1.`id`
  end `id`
  ,T1.`student`
from seat  T1,(SELECT @maxid := (select max(id) maxid from seat)) T2
order by `id`

SQL Fiddle Demo 連結

我先用MSSQL解題@@...

MySQL找時間~

declare @Tmp table(
	id int
	,name nvarchar(50)
)

insert into @Tmp
values(1,'Abbot')
,(2,'Doris')
,(3,'Emerson')
,(4,'Green')
,(5,'Jeames')

select *
from (
	select Row_Number()over(
		order by
		case when id % 2 = 0
		then id - 1
		else id + 1
		end
	) id
	,name
	from @Tmp
) as k
order by id

ok~~追求MySQL版@@~

這次直接用暐翰的語法來改XD

CREATE TABLE seat 
    (`id` int, `student` varchar(7))
;
    
INSERT INTO seat 
    (`id`, `student`)
VALUES
    (1, 'Abbot'),
    (2, 'Doris'),
    (3, 'Emerson'),
    (4, 'Green'),
    (5, 'Jeames')
;

set @i = 0;
select 
  @i := @i + 1 id
  ,T1.`student`
from seat  T1
order by (
  case 
  when (T1.`id` % 2 = 0 ) 
  then T1.`id` -1
  else T1.`id` +1
  end
);

http://sqlfiddle.com/#!9/23fdcf/53

這次寫的效能不是很好，每列都判斷 NULL 就慢了。

SELECT CASE WHEN A.id%2=1 AND B.id IS NULL
            THEN A.id
            WHEN A.id%2=1
			THEN A.id+1
			ELSE A.id-1 END AS id,
	   A.student
FROM seat AS A
LEFT JOIN seat AS B ON B.id=A.id+1
ORDER BY id

create table ithelp180617 (
  id serial primary key
, student text not null  
);

insert into ithelp180617(student) values
('Abbot'), ('Doris'), ('Emerson'), ('Green'), ('James');

--
create or replace function change_seats (ids text[])
returns text[]
as $_$
declare
    rtn_arr text[] default '{}';
    arr_len integer;
    i integer;
    
begin
    arr_len := array_length(ids, 1);
    
    for i in 1..arr_len loop
        if (i % 2) = 0 then
            rtn_arr := array_append(rtn_arr, ids[i-1]);
        else
            rtn_arr := array_append(rtn_arr, ids[i+1]);
        end if;
    end loop;
    
    if rtn_arr[arr_len] is null then
        rtn_arr[arr_len] := ids[arr_len];
    end if;
    return rtn_arr;
end;
$_$ language plpgsql;

----

select unnest(change_seats(array_agg(student))) as "NewSeats"
  from ithelp180617;
  
 NewSeats 
----------
 Doris
 Abbot
 Green
 Emerson
 James
(5 筆資料列)

---------------
號碼不連號一樣能輕鬆處理

truncate table ithelp180617;

insert into ithelp180617(id, student) values
(99, 'Abbot'), (43, 'Doris'), (981, 'Emerson'), (567, 'Green'), (2, 'James');

select unnest(change_seats(array_agg(student))) as "NewSeats"
  from ithelp180617;

 NewSeats 
----------
 Doris
 Abbot
 Green
 Emerson
 James
(5 筆資料列)