문제
Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.
Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.
Input Format
The following tables contain data on the wands in Ollivander's inventory:
•
Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is).
•
Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs, and , then and
.
예시
Sample Input
Wands Table:
Wands_Property Table:
Sample Output
9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1
Plain Text
복사
정답
SELECT W.id as id, P.age as age, W.coins_needed as coins, W.power as power
FROM Wands W LEFT JOIN Wands_Property P
ON W.code = P.code
WHERE P.is_evil = 0 and W.coins_needed = (
SELECT MIN(W1.coins_needed)
FROM Wands W1 LEFT JOIN Wands_Property P1
ON W1.code = P1.code
WHERE W1.power = W.power and P1.age = P.age
)
ORDER BY power DESC, age DESC
;
SQL
복사
풀이
•
WHERE 절 안에 서브쿼리를 사용하는 문제이다.
•
우선 Wands와 Wands_Property를 Wands를 기준으로 JOIN 해준다.
FROM Wands W LEFT JOIN Wands_Property P
ON W.code = P.code
SQL
복사
•
그 다음 출력 조건은 두 개이다.
1.
악한 지팡이가 아닐 것 (is_evil = 0)
2.
동일한 age와 power를 가진 것 중에 최소한의 coin이 필요할 것.
•
is_evil = 0인 것은 P.is_evil = 0 으로 쉽게 구할 수 있다.
•
다음 최소한의 coin이 필요한 지팡이는 서브 쿼리로 구할 수 있다.
W.coins_needed = (
SELECT MIN(W1.coins_needed)
FROM Wands W1 LEFT JOIN Wands_Property P1
ON W1.code = P1.code
WHERE W1.power = W.power and P1.age = P.age
)
SQL
복사
→ 작동 순서를 이해해보자.
•
우선 첫 번째로 FROM 절에 의해 선택된 테이블의 데이터가 위에서부터 선택될 것이다.
•
첫 번째 데이터의 power를 first_power, age를 first_age라고 해보자.
•
그럼 서브쿼리의 WHERE 절에 W.power와 P.age에 각각 first_power와 first_age가 들어갈 것이다.
•
그러면 서브쿼리는 MIN(W1.coins_needed)로 인해 power는 first_power이고 age는 first_age인 데이터들 중에서 코인의 최솟값을 출력할 것이다.
•
나머지는 문제 조건에 맞게 풀면 된다.