对比SQL错误和正确答案 lc619

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.

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

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

没有通过测试，因为没有考虑到note里要求的null。

1 Input: {"headers": {"number": ["num"]}, "rows": {"number": [[8],[1],[8],[3],[4],[3],[1],[4],[5],[5],[6],[6]]}}
2
3 Output: {"headers":["num"],"values":[]}
4
5 Expected: {"headers":["num"],"values":[[null]]}

因此，若要输出null，需要满足两个条件，第一，select中应是聚合函数，第二，聚合函数处理的行数应为0。
修改后用聚合函数MAX()通过

Solution:
SELECT MAX(num) AS num FROM
(SELECT num FROM number
. Waral dи,GROUP BY num
HAVING COUNT(*)=1)
AS tmp;

这种题目难道不是用窗口函数？ Top N, bottom N， 无论有没有NULL都可轻松解决。

赞一下，楼主加油💪