2021-04-30

只出现一次的最大数字

需求：编写一个 SQL 查询，找到只出现过一次的数字中，最大的一个数字。如果没有只出现一次的数字，输出 null 。

展示效果：

num
6

Create table If Not Exists 37_my_numbers (num int);
Truncate table 37_my_numbers;
insert into 37_my_numbers (num) values (8);
insert into 37_my_numbers (num) values (8);
insert into 37_my_numbers (num) values (3);
insert into 37_my_numbers (num) values (3);
insert into 37_my_numbers (num) values (1);
insert into 37_my_numbers (num) values (4);
insert into 37_my_numbers (num) values (5);
insert into 37_my_numbers (num) values (6);

最终SQL:

select 
      ifnull((SELECT 
                    num
              FROM 
                    37_my_numbers
              group by 
                    num
              having
                    count(*) = 1
              order by 
                    num desc
              limit 1), null) as num;