[HackerRank Medium] Symmetric Pairs [MySQL]

https://www.hackerrank.com/challenges/symmetric-pairs/forum

Discussion on Symmetric Pairs Challenge

You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

Sample Input

Sample Output

20 20
20 21
22 23

문제이해:

 

(X1, Y1) = (Y2, X1)인 것을 찾아서 X1을 기준으로 오름차순 정렬하라. 출력시 X1<=Y2이다.

 

생각의 흐름:

 

(X1, Y1) = (Y2, X1)일 때를 WHERE을 사용하여 나타내면 되겠네

-> 어? 그런데 X1과 Y1이 값이 같으면 어떡 하지?

-> 서브 쿼리에 X1과 Y1이 같은 것의 개수를 표현하여 2보다 크면 출력하자! 

SELECT DISTINCT F1.X, F1.Y
FROM Functions AS F1, Functions AS F2
WHERE (F1.X < F1.Y AND ((F2.Y = F1.X) AND (F2.X = F1.Y))) OR
(F1.X = F1.Y AND (SELECT COUNT(*)
                 FROM Functions AS F3
                 WHERE ((F3.Y = F1.X) AND (F3.X = F1.Y))) >= 2)
ORDER BY F1.X