LeetCode 619. Biggest Single Number

最新推荐文章于 2023-07-21 13:47:46 发布

chelseajcole

最新推荐文章于 2023-07-21 13:47:46 发布

阅读量551

点赞数

本文介绍了一种SQL查询技巧，即如何从包含重复数字的数据表中找出只出现一次的最大数值。通过使用WITH语句创建一个临时视图，该视图过滤出所有不重复的数字，然后从中选取最大的一个。

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.

create table number (num int)

insert into number values(8)
insert into number values(8)
insert into number values(3)
insert into number values(3)
insert into number values(1)
insert into number values(4)
insert into number values(5)
insert into number values(6)

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

select * from number

with cte as(
select num from number
group by num
having count(num)=1
)select max(num) as Num from cte