組合兩個表

SELECT 
    FirstName, LastName, City, State
FROM
    Person a
        LEFT JOIN
    Address b ON a.PersonId = b.PersonId;

第二高的薪水

SELECT 
    CASE
        WHEN FirstHighestSalary = SecondHighestSalary THEN NULL
        ELSE SecondHighestSalary
    END AS SecondHighestSalary
FROM
    (SELECT 
        MIN(Salary) AS SecondHighestSalary,
            MAX(Salary) AS FirstHighestSalary
    FROM
        (SELECT 
        *
    FROM
        Employee
    ORDER BY Salary DESC
    LIMIT 2) a) a;

第N高的薪水

SELECT 
    CASE
        WHEN rank_min < N THEN NULL
        ELSE a.Salary
    END AS salary
FROM
    (SELECT 
        Salary, MIN(rank) AS rank_min
    FROM
        (SELECT 
        a.Salary, rank
    FROM
        (SELECT 
        Salary
    FROM
        (SELECT 
        Salary, @row_num:=@row_num + 1 AS rank
    FROM
        (SELECT 
        *, @row_num:=0
    FROM
        Employee
    ORDER BY Salary DESC) a) a
    WHERE
        rank = N) a
    INNER JOIN (SELECT 
        Salary, @row_num:=@row_num + 1 AS rank
    FROM
        (SELECT 
        *, @row_num:=0
    FROM
        Employee
    ORDER BY Salary DESC) a) b ON a.Salary = b.Salary) a) a;

分數(shù)排名

SELECT 
    a.Score, rank
FROM
    (SELECT 
        Score, num, @row_num:=@row_num + 1 AS rank
    FROM
        (SELECT 
        Score, COUNT(1) AS num, @row_num:=0
    FROM
        Scores
    GROUP BY Score
    ORDER BY Score DESC) a) a
        INNER JOIN
    Scores b ON a.Score = b.Score
ORDER BY a.Score DESC; 

連續(xù)出現(xiàn)的數(shù)字

SELECT DISTINCT
    c.Num AS ConsecutiveNums
FROM
    (SELECT 
        Num, @row_num:=@row_num + 1 AS rank
    FROM
        (SELECT 
        *, @row_num:=0
    FROM
        Logs) a) a
        LEFT JOIN
    (SELECT 
        Num, @row_num:=@row_num + 1 AS rank
    FROM
        (SELECT 
        *, @row_num:=0
    FROM
        Logs) a) b ON a.rank = b.rank + 1
        LEFT JOIN
    (SELECT 
        Num, @row_num:=@row_num + 1 AS rank
    FROM
        (SELECT 
        *, @row_num:=0
    FROM
        Logs) a) c ON a.rank = c.rank + 2
WHERE
    a.Num = b.Num AND a.Num = c.Num;

超過經(jīng)理收入的員工

SELECT 
    a.Name AS Employee
FROM
    (SELECT 
        Name, Salary, ManagerId
    FROM
        Employee
    WHERE
        ManagerId IS NOT NULL) a
        LEFT JOIN
    (SELECT 
        Id, Salary
    FROM
        Employee) b ON a.ManagerId = b.id
WHERE
    a.Salary > b.Salary 

查找重復(fù)的電子郵箱

SELECT 
    Email
FROM
    (SELECT 
        Email, COUNT(1) AS num
    FROM
        Person
    GROUP BY Email) a
WHERE
    a.num > 1

從不訂購的客戶

SELECT 
    a.Name AS Customers
FROM
    Customers a
        LEFT JOIN
    Orders b ON a.Id = b.CustomerId
WHERE
    b.CustomerId IS NULL

部門工資最高的員工

SELECT 
    c.Name AS Department, b.Name AS Employee, Salary
FROM
    (SELECT 
        DepartmentId, MAX(Salary) AS max_salary
    FROM
        (SELECT 
        DepartmentId, salary
    FROM
        Employee) a
    GROUP BY DepartmentId) a
        INNER JOIN
    (SELECT 
        Name, Salary, DepartmentId
    FROM
        Employee) b ON a.DepartmentId = b.DepartmentId
        AND a.max_salary = b.Salary
        INNER JOIN
    Department c ON a.DepartmentId = c.Id

換座位

SELECT
    id - 1 AS id,
    student
FROM
    seat
WHERE
    id MOD 2 = 0
UNION
    SELECT
        CASE
    WHEN id + 1 <= id_max THEN
        id + 1
    ELSE
        id
    END AS id,
    student
FROM
    (
        SELECT
            *
        FROM
            seat
        LEFT JOIN (SELECT max(id) AS id_max FROM seat) c ON seat.id <= c.id_max
    ) a
WHERE
    id MOD 2 = 1
ORDER BY
    id