Database Leetcode习题集解析

来源：互联网发布：有赞api php 调用实例编辑：程序博客网时间：2024/05/22 12:56

<1>Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+| Id | Email   |+----+---------+| 1  | a@b.com || 2  | c@d.com || 3  | a@b.com |+----+---------+

For example, your query should return the following for the above table:

+---------+| Email   |+---------+| a@b.com |+---------+

Solution:

# 找到person表中重复的Email
# 方案一：
# SELECT Email FROM Person GROUP BY Email WHERE count(*)>1 #由于合计函数 GROUP BY不能和 WHERE 同时使用所以引入HAVING。
SELECT Email FROM Person GROUP BY Email HAVING count(*)>1

# 方案二
#使用JOIN ON
SELECT DISTINCT a.Email FROM Person aINNER JOIN Person b ON (a.Email = b.Email) WHERE a.Id != b.Id

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<2>Combine Two Tables

Table: Person

+-------------+---------+| Column Name | Type    |+-------------+---------+| PersonId    | int     || FirstName   | varchar || LastName    | varchar |+-------------+---------+PersonId is the primary key column for this table.

Table: Address

+-------------+---------+| Column Name | Type    |+-------------+---------+| AddressId   | int     || PersonId    | int     || City        | varchar || State       | varchar |+-------------+---------+AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

Solution:

# Write your MySQL query statement below
SELECT Person.FirstName,Person.LastName,Address.City,Address.StateFROM PersonLEFT JOIN Address ON Person.personId = Address.PersonId;

<3>Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+| Id | Name  | Salary | ManagerId |+----+-------+--------+-----------+| 1  | Joe   | 70000  | 3         || 2  | Henry | 80000  | 4         || 3  | Sam   | 60000  | NULL      || 4  | Max   | 90000  | NULL      |+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+| Employee |+----------+| Joe      |+----------+

Solution:

SELECT e1.NAME as Employee FROM Employee e1 ,Employee e2 where e1.ManagerId = e2.Idand e1.Salary>e2.Salary

<4>Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+| Id | Salary |+----+--------+| 1  | 100    || 2  | 200    || 3  | 300    |+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

Solution:

SELECTIFNULL( (SELECTDISTINCT SalaryFROM EmployeeORDERBY SalaryDESCLIMIT 1,1) ,NULL)AS SecondHighestSalary

<5>Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+| Id | Salary |+----+--------+| 1  | 100    || 2  | 200    || 3  | 300    |+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

Solution:

CREATEFUNCTION getNthHighestSalary(N INT)RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
SELECTIFNULL((SELECTDISTINCT SalaryFROM EmployeeORDER BY Salary DESC LIMIT M,1),NULL)AS NHighestSalary
);
END

0 0