619-BiggestSingleNumber.sql

-- 619. Biggest Single Number
-- Table: MyNumbers
-- +-------------+------+
-- | Column Name | Type |
-- +-------------+------+
-- | num         | int  |
-- +-------------+------+
-- There is no primary key for this table. It may contain duplicates.
-- Each row of this table contains an integer.
-- A single number is a number that appeared only once in the MyNumbers table.
-- Write an SQL query to report the largest single number. If there is no single number, report null.
-- The query 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.

-- Create table If Not Exists MyNumbers (num int)
-- Truncate table MyNumbers
-- insert into MyNumbers (num) values ('8')
-- insert into MyNumbers (num) values ('8')
-- insert into MyNumbers (num) values ('3')
-- insert into MyNumbers (num) values ('3')
-- insert into MyNumbers (num) values ('1')
-- insert into MyNumbers (num) values ('4')
-- insert into MyNumbers (num) values ('5')
-- insert into MyNumbers (num) values ('6')

-- Write your MySQL query statement below
-- max
SELECT
    MAX(num) AS num
FROM
    ( -- 求出一出现一次的所有数字
        SELECT
            num
        FROM
            MyNumbers
        GROUP By
            num
        HAVING
            COUNT(*) = 1
    ) AS a

-- limit
SELECT
    num
FROM
    ( -- 求出一出现一次的所有数字
        SELECT
            num
        FROM
            MyNumbers
        GROUP By
            num
        HAVING
            COUNT(*) = 1
    ) AS a
ORDER BY
    num DESC 
LIMIT 
    1