SQL架构
小美是一所中学的信息科技老师,她有一张 seat
座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
示例:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表,则输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
思路:1、2互换,3、4互换,最后为奇数则不换。
select id,d_name from (
select d_id-1 as id,d_name from deenslton where mod(d_id,2)=0 -- 求出偶数 并向前挪了一步
union
select d_id+1 as id,d_name from deenslton where mod(d_id,2)=1 and d_id!=(select count(*) from deenslton) -- 同上 求出奇数 并向下挪了一步
union
select d_id as id,d_name from deenslton where mod(d_id,2)=1 and d_id=(select count(*) from deenslton)
) order by id
这道题真好。知识点:mod(m,n) ----- 取余的mod ,= m/n . union 链接满足条件的所有数据。
答案:
select id,student from (
select id-1 as id,student from seat where mod(id,2)=0 -- 求出偶数 并向前挪了一步
union
select id+1 as id,student from seat where mod(id,2)=1 and id!=(select count(*) from seat) -- 同上 求出奇数 并向下挪了一步
union
select id as id,student from seat where mod(id,2)=1 and id=(select count(*) from seat)
) order by id
还可以用case when 写,不在这里例举了....