MYSQL力扣练习题集合

[toc]

limit 1 offset 1 不存在却要求返回null时，可以用子查询，或者IFNULL(临时表结果或字段， NULL)
子查询是select结果作为字段，例如 select (select xx) 而不是 select xxx from (select xxx)。当select中的字段结果不存在，会自动返回NULL

如果排名问题要去重，记得加 distinct

CREATE FUNCTION中
可以set N:=N-1: 进行变量更新。不能在return中进行更新

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      select (select distinct salary from Employee 
      order by salary desc  
      limit 1 offset N) 
  );
END

要做排名时给出序号时，有两种方式
简单方式：
利用子查询字段，注意子查询字段的特点等同于摘出一个字段，再做一次额外查询，得到唯一的一个结果。

# Write your MySQL query statement below

select  a.score, (
    # 拿a.score重新重新去检索一次表，得到count(distinct b.score)+1，得到自己的排名
    select count(distinct b.score)+1 from Scores b where  a.score<b.score
    ) as `rank` 
    
    from Scores a order by a.score desc

但效率太慢。
高速的序号方法：
使用dense_rank() over(order by xxx) 可以得到序号，1 1 2 3 3 4，不会跳序号
其他序号方式：
row_number() over(order by xxx) 就是简单的12345这种，不考虑重复
rank() over(order by xxx) 如果重复了，1 1 3 4 4 6

1
2
3

select  score, 
(dense_rank() over(order by score desc) ) as `rank` 
from Scores

寻找3个连续出现的数字，如果id是连续的，则可以自联结，即自己和自己join，构造where a.id = b.id-1 and b.id = c.id-1 这样的情况。非常好理解

select distinct a.num as ConsecutiveNums from 
logs a,logs b, logs c
where 
a.id = b.id-1
and b.id = c.id-1
and a.num = b.num and b.num = c.num

善用自join联结解决一些表内成员互相比较的复杂问题。

# 查询收入超过他经理的员工名字。
select 
 a.name as Employee 
from 
Employee  a , Employee  b
where b.id = a.managerId and a.salary > b.salary

group分组后，可以用having进行组内聚合情况的过滤，剔除不想要的组

# 查找 Person 表中所有重复的电子邮箱。
select email from person 
group by email 
having 
count(email) > 1;

表a在表b中不存在记录，用left join + where xxx is null 来处理

1
2
3

select c.name as Customers 
from Customers c  left join  Orders  o
on c.id = o.CustomerId where o.id is null

in 的用法 where a in(1,2,3,4)
或者 where a in (select aaa from xxx)

in也支持二元组形式
where (a,b) in (select aaa,bbb from xxx)

每组最大值的人选的情况，可以用join + in（分组结果）的方法

# 查找每个部门中薪资最高的员工，先join，再用in获取符合组+最大值的行
select 
b.name as Department , a.name   as Employee ,  a.salary as Salary
from Employee  a 
join
Department  b
on a.departmentId  = b.id  
and (a.departmentId, a.salary) in (
    select departmentId, max(salary)
    from Employee
    group by departmentId 
)

找某薪水是组内前几位的情况，擅用自查询，即join后，拿某条记录在where中遍历一遍, 然后根据count(distinct xxx)来确认自己的排名

# 找出每个部门获得前三高工资的所有员工
select 
b.name as Department , a.name   as Employee ,  a.salary as Salary
from Employee  a 
join
Department  b
on a.departmentId  = b.id
# 比自己这行记录salary大， 且在同一组内的数量小于3，则满足前3  
and (select count(distinct e.salary) from Employee e where a.DepartmentId  = e.DepartmentId  and a.salary < e.salary
 ) < 3

分组后， SUM(IF(x=y,1,0) 可以统计组内x=y的个数
那么SUM(IF(x=y,1,0) ） / COUNT(x) 就能得到组内x=y的比率
ROUND(xxx, 2) 可以保留两位小数。
between的用法： between ‘2013-10-01’ AND ‘2013-10-03’

# 写一段 SQL 语句查出?"2013-10-01"?至?"2013-10-03"?期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。

select request_at as DAY, ROUND(
    SUM(IF(status = 'completed', 0, 1))/COUNT(status), 2
) as `Cancellation Rate`
 from 
Trips t, Users u1, Users u2
where (t.client_id  = u1.users_id
and t.driver_id = u2.users_id)
and u1.banned = 'No' and u2.banned = 'No' 
and  request_at between '2013-10-01' AND '2013-10-03'
group by request_at

求3个或3个以上连续id的记录，直接自联结，然后用3个or条件判断
t1.id t2.id t3.id
t2.id t1.id t3.id
t2.id t3.id t1.id
然后取每种连接表的t1即可

注意distinct 可以用于星号
distinct s1.*
也可用于多列 distict s1.a,s1.b,s1.completed

# 找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
# 返回按 visit_date 升序排列的结果表
select 
distinct s1.id,s1.visit_date , s1.people            
from 
Stadium s1, Stadium s2,Stadium s3
where s1.people  >= 100 and s2.people >=100 and s3.people >= 100 
and (
    (s1.id = s2.id-1 and s2.id = s3.id-1 ) or
(s2.id = s1.id-1 and s1.id = s3.id-1 ) or
(s2.id = s3.id-1 and s3.id = s1.id-1 )
)
order by s1.id