【高频SQL基础题】626.换座位

题目：

自寻。在力扣上。

解题：

通过此题，检查出自己不熟悉的多个知识点。

答案在最后。

1.ROW_NUMBER 的格式

row_number() over (partition by order by )

比如，根据id，给每行再加一个窗口num，num是连续的

SELECT *,ROW_NUMBER() over (ORDER by id) as row_num   from seat

2.标红的“as subquery” 如何不写，就会报错：

Every derived table must have its own alias。

推荐在所有的子查询时，给它起个名字，防止语法出错

SELECT case when t.num % 2 = 1 and t.num = (SELECT max(num) from (SELECT *,ROW_NUMBER() over (ORDER by id) as num from seat) as subquery ) then t.id

when t.num % 2 = 1 then t.id+1  

else t.num -1  end as id ,student

from (SELECT *,ROW_NUMBER() over (ORDER by id)  as num from seat) t

order by id

3.case when 的用法

case when 条件1 then 返回值1

when 条件2 then 返回值2

else 返回值3

end as 别名

4.最大值与case when 的连用

t.id= (SELECT max(id) from seat)

5.order by 的排序，不是想当然的

下面这两个，其他都一样，仅最后order by 不一样。

一个是t.id，一个是个id，但是差之毫厘，失之千里。

他们的结果是不一样的！要小心啊

SELECT

CASE WHEN t.id % 2 = 1 AND t.id = (SELECT MAX(id) FROM  seat) THEN t.id

WHEN t.id % 2 = 1 THEN t.id + 1

ELSE t.id - 1

END AS id,t.student

FROM

seat t

ORDER BY t.id;

参考答案：

SELECT

CASE WHEN t.id % 2 = 1 AND t.id = (SELECT MAX(id) FROM  seat) THEN t.id

WHEN t.id % 2 = 1 THEN t.id + 1

ELSE t.id - 1

END AS id,t.student

FROM

seat t

ORDER BY id;

如果要再考虑id可能不连续的情况，就得用这个SQL：

SELECT case when t.num % 2 = 1 and t.num = (SELECT max(num) from (SELECT *,ROW_NUMBER() over (ORDER by id) as num from seat) as subquery ) then t.id

when t.num % 2 = 1 then t.id+1  

else t.num -1  end as id ,student

from (SELECT *,ROW_NUMBER() over (ORDER by id)  as num from seat) t

order by id