LeetCode Database 题目

175.

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

176.

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee 
WHERE Salary 
NOT IN (SELECT MAX(Salary) FROM Employee);

SELECT
    ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1)
SecondHighestSalary;

177.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT N,1
  );
END

178. (*)

SELECT
    S1.Score,
    Count(DISTINCT S2.Score) rank
FROM
    Scores S1 INNER JOIN
    Scores S2
    ON S1.Score <= S2.Score
GROUP BY
    S1.id 
ORDER BY
    S1.Score DESC;

180.

SELECT 
    DISTINCT Num ConsecutiveNums 
FROM
    Logs L1
WHERE
    Num=(SELECT Num FROM Logs WHERE Id=L1.Id+1)
    AND
    Num=(SELECT Num FROM Logs WHERE Id=L1.Id+2);

181.

SELECT E1.Name Employee
FROM Employee E1 INNER JOIN Employee E2
ON E1.ManagerId = E2.Id
WHERE E1.Salary > E2.Salary;

182.

SELECT Email
FROM Person
GROUP BY Email
HAVING Count(Email) > 1;

183.

SELECT Name Customers
FROM Customers
WHERE Id NOT IN 
(SELECT CustomerId FROM Orders);

SELECT C1.Name Customers
FROM Customers C1 LEFT JOIN Orders O1
ON C1.Id = O1.CustomerId
WHERE O1.CustomerId IS null;