Leetcode 619. Biggest Single Number

Problem:

Table number contains many numbers in column num including duplicated ones.
Can you write a SQL query to find the biggest number, which only appears once.

+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 | 

For the sample data above, your query should return the following result:

+---+
|num|
+---+
| 6 |

Note:
If there is no such number, just output null.

Solution:

　　这道题本不难，但引申出了一个很重要的问题，我第一次写的答案是

SELECT num FROM number GROUP BY num HAVING COUNT(*)=1 ORDER BY num DESC LIMIT 1;

　　但没有通过测试用例

Input: {"headers": {"number": ["num"]}, "rows": {"number": [[8],[1],[8],[3],[4],[3],[1],[4],[5],[5],[6],[6]]}}

Output: {"headers":["num"],"values":[]}

Expected: {"headers":["num"],"values":[[null]]}

　　因此，若要输出null，需要满足两个条件，第一，select中应是聚合函数，第二，聚合函数处理的行数应为0。

Code:

 

SELECT MAX(num) AS num FROM (SELECT num FROM number GROUP BY num HAVING COUNT(*)=1) AS T;