leetcode数据库题sql题解(持更)(MYSQL)

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 (select Max(Salary)from Employee) > Salary

177.第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN SET N:=N-1; 
  RETURN (
      # Write your MySQL query statement below.     
      select Salary
      FROM Employee
      group by Salary
      order by Salary DESC
      Limit N,1
  );
END

178.分数排名

SELECT Score, dense_rank() over (order by Score DESC) as `Rank`
from Scores;

180.连续出现的数字

select distinct a.Num as ConsecutiveNums 
from Logs as a,
   Logs as b,
   Logs as c
where a.Id = b.Id - 1 and b.Id = c.Id - 1 and a.Num= b.Num and b.Num = c.Num;

181.超过经理收入的员工

select e1.Name as Employee
from Employee as e1,Employee as e2
where e1.ManagerId = e2.Id and e1.Salary > e2.Salary

182.查找重复的电子邮箱

select Email 
from Person
group by Email
having Count(Id)>1

183.从不订购的客户

左连接两个表，找出Orders.Id为空的

select Customers.Name as Customers
from Customers
left join Orders on (Customers.Id = Orders.CustomerId)
where Orders.Id is null

184.部门工资最高的员工

SELECT
    Department.name AS 'Department',Employee.name AS 'Employee',Salary
FROM
    (Employee inner JOIN Department ON Employee.DepartmentId = Department.Id)
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)

185.部门工资前三高的员工

内连接之后使用窗口函数用dense_rank()排序，Department分组，然后显示出分别dense_rank()小于或等于3的人

select Department,Employee,Salary
FROM
(SELECT Department.name AS 'Department',Employee.name AS 'Employee',Salary,dense_rank() over(partition by Department.name order by Salary desc) as dense_num
FROM (Employee inner JOIN Department ON Employee.DepartmentId = Department.Id)) as a
where dense_num<=3

196.删除重复的

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id