力扣SQL仅数据库(175~185)

175. 组合两个表

题目：编写解决方案，报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为 null 

准备数据：

Create table If Not Exists Person (personId int, firstName varchar(255), lastName varchar(255))
Create table If Not Exists Address (addressId int, personId int, city varchar(255), state varchar(255))
Truncate table Person
insert into Person (personId, lastName, firstName) values ('1', 'Wang', 'Allen')
insert into Person (personId, lastName, firstName) values ('2', 'Alice', 'Bob')
Truncate table Address
insert into Address (addressId, personId, city, state) values ('1', '2', 'New York City', 'New York')
insert into Address (addressId, personId, city, state) values ('2', '3', 'Leetcode', 'California')

代码实现：

select   firstname,lastname,city,state 
from person left join address 
on person.personid=address.personid;

176. 第二高的薪水

题目：查询并返回 Employee 表中第二高的 不同 薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。

准备数据：

Create table If Not Exists Employee (id int, salary int)
Truncate table Employee
insert into Employee (id, salary) values ('1', '100')
insert into Employee (id, salary) values ('2', '200')
insert into Employee (id, salary) values ('3', '300')

代码实现：

select max(salary) SecondHighestSalary from 
(select id,salary,dense_rank() over (order by salary desc) a from employee) t1 
where t1.a=2;

177. 第N高的薪水

题目：查询 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询结果应该为 null 。

准备数据：

Create table If Not Exists Employee (Id int, Salary int)
Truncate table Employee
insert into Employee (id, salary) values ('1', '100')
insert into Employee (id, salary) values ('2', '200')
insert into Employee (id, salary) values ('3', '300')

代码实现：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
     # Write your MySQL query statement below.
    select  max(t.salary) 
    from 
    (select salary,dense_rank() over(order by salary desc)ran from employee)t 
    where t.ran=N 
  );
END

178. 分数排名

题目：

编写一个解决方案来查询分数的排名。排名按以下规则计算:

• 分数应按从高到低排列。
• 如果两个分数相等，那么两个分数的排名应该相同。
• 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表

准备数据：

Create table If Not Exists Scores (id int, score DECIMAL(3,2))
Truncate table Scores
insert into Scores (id, score) values ('1', '3.5')
insert into Scores (id, score) values ('2', '3.65')
insert into Scores (id, score) values ('3', '4.0')
insert into Scores (id, score) values ('4', '3.85')
insert into Scores (id, score) values ('5', '4.0')
insert into Scores (id, score) values ('6', '3.65')

代码实现：

使用窗口函数：mysql版本在8.0之后

select score,dense_rank() over (order by score desc) `rank` from scores;

不使用窗口函数：mysql版本在8.0以前

第一步：将scores表进行自连接，连接条件为第一个scores表的分数大于第二个scores表的分数

select * from (select distinct score from scores)s1
    join (select distinct score from scores)s2 
on  s1.score >=s2.score;

第二步：统计第一个scores表的各个分数大于等于第二个score表的分数的个数，此时数值越大说明该分数就越高，而我们想要的是数值越小，分数越高。所以这里不对s1.score进行分组，而是对s2.score进行分组，有多少个s2.score的出现说明有多少个s1.score的值大于等于它，也即是该数字排行第几

select s2.score sco,count(s1.score) con from 
    (select distinct score from scores)s1
    join (select distinct score from scores) s2 
on  s1.score >=s2.score  
group by s2.score;

 第三步：第二步得出的分数是去重后的，因此需要再连接回scores表找出全部的分数

select score,con as 'rank' from scores left join (
    select s2.score sco,count(s1.score) con 
        from (select distinct score from scores)s1
        join (select distinct score from scores)s2 
        on  s1.score >=s2.score 
    group by s2.score)t
on  scores.score=t.sco order by con;

180. 连续出现的数字

题目：找出所有至少连续出现三次的数字。返回的结果表中的数据可以按 任意顺序 排列。

数据准备：

Create table If Not Exists Logs (id int, num int)
Truncate table Logs
insert into Logs (id, num) values ('1', '1')
insert into Logs (id, num) values ('2', '1')
insert into Logs (id, num) values ('3', '1')
insert into Logs (id, num) values ('4', '2')
insert into Logs (id, num) values ('5', '1')
insert into Logs (id, num) values ('6', '2')
insert into Logs (id, num) values ('7', '2')

思路·：

代码实现：

with t as (select *,rank() over(order by id) ids,
                    cast(rank()over(partition by num order by id) as signed)num1 from logs)
select distinct num ConsecutiveNums from t group by num,id-num1 having count(id-num1)>=3;

181. 超过经理收入的员工

题目：编写解决方案，找出收入比经理高的员工。

数据准备：

Create table If Not Exists Employee (id int, name varchar(255), salary int, managerId int)
Truncate table Employee
insert into Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3')
insert into Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4')
insert into Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', 'None')

代码实现：

select e1.name employee 
from employee e1,employee e2 
where e1.managerId=e2.id and e1.salary>e2.salary ;

182. 查找重复的电子邮箱

题目：编写解决方案来报告所有重复的电子邮件。 请注意，可以保证电子邮件字段不为 NULL

数据准备：

Create table If Not Exists Person (id int, email varchar(255))
Truncate table Person
insert into Person (id, email) values ('1', 'a@b.com')
insert into Person (id, email) values ('2', 'c@d.com')
insert into Person (id, email) values ('3', 'a@b.com')

代码实现：

select email from person group by email having count(email)>1;

183. 从不订购的客户

题目：找出所有从不点任何东西的顾客。

数据准备：

Create table If Not Exists Customers (id int, name varchar(255))
Create table If Not Exists Orders (id int, customerId int)
Truncate table Customers
insert into Customers (id, name) values ('1', 'Joe')
insert into Customers (id, name) values ('2', 'Henry')
insert into Customers (id, name) values ('3', 'Sam')
insert into Customers (id, name) values ('4', 'Max')
Truncate table Orders
insert into Orders (id, customerId) values ('1', '3')
insert into Orders (id, customerId) values ('2', '1')

代码实现：

ifnull 判空，如果为空时返回0

select name customers from (
    select Customers.*,ifnull(Orders.id,0) oid 
    from customers left join orders 
    on Customers.id=Orders.customerId)t 
where t.oid=0;

184. 部门工资最高的员工

题目：查找出每个部门中薪资最高的员工

数据准备：

Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int)
Create table If Not Exists Department (id int, name varchar(255))
Truncate table Employee
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (id, name, salary, departmentId) values ('2', 'Jim', '90000', '1')
insert into Employee (id, name, salary, departmentId) values ('3', 'Henry', '80000', '2')
insert into Employee (id, name, salary, departmentId) values ('4', 'Sam', '60000', '2')
insert into Employee (id, name, salary, departmentId) values ('5', 'Max', '90000', '1')
Truncate table Department
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')

代码实现：

select Department.name department,t.name employee,salary from
     (select *,max(salary)over(partition by departmentId) maxsalary from employee )t
join department on t.departmentId=department.id where salary=maxsalary;

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

题目：编写解决方案，找出每个部门中 收入高的员工 。

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

数据准备：

Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int)
Create table If Not Exists Department (id int, name varchar(255))
Truncate table Employee
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2')
insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2')
insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1')
insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '1')
insert into Employee (id, name, salary, departmentId) values ('6', 'Randy', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('7', 'Will', '70000', '1')
Truncate table Department
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')

代码实现：

select department.name department,t.name employee,t.salary salary from (
    select name,salary,departmentId,
    dense_rank() over (partition by departmentId order by salary desc) ran 
    from Employee)t 
join Department on t.departmentId=Department.id where ran<=3;