Create exchange-seats.sql

kamyu104 · web-flow · commit b16e6a3c4c7b · 2017-10-04T22:54:39.000+08:00
diff --git a/MySQL/exchange-seats.sql b/MySQL/exchange-seats.sql
@@ -0,0 +1,40 @@
+# Time:  O(nlogn)
+# Space: O(n)
+
+# Mary is a teacher in a middle school and she has a table seat storing
+# students' names and their corresponding seat ids.
+#
+# The column id is continuous increment.
+# Mary wants to change seats for the adjacent students.
+# Can you write a SQL query to output the result for Mary?
+# +---------+---------+
+# |    id   | student |
+# +---------+---------+
+# |    1    | Abbot   |
+# |    2    | Doris   |
+# |    3    | Emerson |
+# |    4    | Green   |
+# |    5    | Jeames  |
+# +---------+---------+
+#
+# For the sample input, the output is:
+# +---------+---------+
+# |    id   | student |
+# +---------+---------+
+# |    1    | Doris   |
+# |    2    | Abbot   |
+# |    3    | Green   |
+# |    4    | Emerson |
+# |    5    | Jeames  |
+# +---------+---------+
+#
+# Note:
+# If the number of students is odd, there is no need to change the last one's seat.
+
+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;
