[LeetCode] 619. Biggest Single Number - SQL

링크: https://leetcode.com/problems/biggest-single-number/

Table: MyNumbers

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
+-------------+------+
This table may contain duplicates (In other words, there is no primary key for this table in SQL).
Each row of this table contains an integer.

A single number is a number that appeared only once in the MyNumbers table.

Find the largest single number. If there is no single number, report null.

The result format is in the following example.

Example 1:

Input:
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 3   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
+-----+
Output:
+-----+
| num |
+-----+
| 6   |
+-----+
Explanation: The single numbers are 1, 4, 5, and 6.
Since 6 is the largest single number, we return it.

Example 2:

Input:
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 7   |
| 7   |
| 3   |
| 3   |
| 3   |
+-----+
Output:
+------+
| num  |
+------+
| null |
+------+
Explanation: There are no single numbers in the input table so we return null.

---

문제풀이

목표: single number는 MyNumbers 테이블에서 오직 한 번 나타나는 숫자이다. 가장 큰 single number를 찾아라

• single number가 없다면, null 을 보고.

최종코드

SELECT MAX(num) as num
FROM MyNumbers
WHERE num IN (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) < 2
    )

설명

FROM, WHERE 절

FROM MyNumbers
WHERE num IN (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) < 2
    )

• MyNumbers 테이블에서
• num 이 서브쿼리의 결과 내에 있는 것만 필터링
  • 서브쿼리:
    • MyNumbers 테이블에서
    • num 을 기준으로 그룹화 하고
    • num의 COUNT 가 2 미만인 것을 고른다(1번만 나타나는)

SELECT 절

SELECT MAX(num) as num

• WHERE 절에서 필터링한 num 의 최대값을 조회하면 끝!