做SQL题的时候有一道交换id的题,解法中用到了^,第一次见,总结一下。
^其实就是按位异或.
在本题中用异或处理奇偶id:(s1.id+1)^1-1
id为2,处理后得到1
(2 + 1) ^ 1 -1
in binary: (10 +1 ) ^ 1 - 1
11 ^ 01 - 1 = 10 - 1 = 1
id为3,处理后得到4
(3 + 1 ) ^ 1 -1
in binary: (11 + 1) ^ 1 -1
100 ^ 001 - 1
101 - 1 = 4
3 in binary = 11, add 1 = 100, 即 100 ^ 1 即 100 xor 001,注意此处是按位异或,根据规则(相同的数异或后返回0,不同的数异或后返回1),因此返回101,即5,101 - 1 = 100 即4。
code:
select s1.id, coalesce(s2.student, s1.student) as student
from seat s1 Left Join seat s2
on (s1.id + 1) ^ 1 - 1 = s2.id
order by s1.id
顺便记录一下 coalesce(), return the first element in the list is not null, 在这个题里take care of the last odd id.