高频SQL50题（基础题）-5

主要内容

1. LeetCode-高频SQL50题 41-50

一.SQL练习题

1.602-好友申请：谁有最多的好友

代码如下（示例）:

# Write your MySQL query statement below
select id,count(*) num 
from (
    (
        select requester_id id
        from requestAccepted
    )
    union all
    (
        select accepter_id id
        from requestAccepted
    )
) tt
group by id 
order by num desc
limit 1 ;

2.585-2016年的投资

代码如下（示例）:

# Write your MySQL query statement below
select round(sum(tiv_2016),2) tiv_2016
from(
    select *,
    count(pid) over (partition by tiv_2015) as num_tiv,
    count(pid) over (partition by lat,lon) as num_city
    from insurance
)h
where h.num_tiv >1 and num_city =1;

3.185-部门工资前三高的所有员工

代码如下（示例）:

# Write your MySQL query statement below
select Department,Employee,Salary
from (
    select d.Name as Department,
        e.Name as Employee,
        e.Salary as Salary,
    dense_rank() over ( partition by DepartmentId order by Salary desc) as rk 
    from Employee as e, Department as d 
    where e.DepartmentId = d.Id
) m 
where rk <=3;

法2：
select d.Name as Department,e.Name as Employee,e.Salary as Salary
from Employee as e left join Department as d 
on e.DepartmentId = d.Id
where e.Id in
(
    select e1.Id
    from Employee as e1 left join Employee as e2
    on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
    group by e1.Id
    having count(distinct e2.Salary) <= 2
)
and e.DepartmentId in (select Id from Department)
order by d.Id asc,e.Salary desc

4.1667-修复表中的名字

代码如下（示例）:

# Write your MySQL query statement below
select user_id,
    concat(upper(substr(name,1,1)),lower(substr(name,2))) name
from users
order by user_id;

5.1527-患某种疾病的患者

代码如下（示例）:

# Write your MySQL query statement below
select patient_id,patient_name,conditions
from patients
where conditions like '%DIAB1'
or conditions like 'DIAB1%'
or conditions like '% DIAB1%';

6.196-删除重复的电子邮箱

代码如下（示例）:

# Write your MySQL query statement below
delete u
from Person u , Person v
where v.id < u.id and u.email = v.email 

7.176-第二高的薪水

代码如下（示例）:

# Write your MySQL query statement below
select ifNull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary;

 要想获取第二高，需要排序，使用 order by（默认是升序 asc，即从小到大），若想降序则使用关键字 desc

 去重，如果有多个相同的数据，使用关键字 distinct 去重

 判断临界输出，如果不存在第二高的薪水，查询应返回 null，使用 ifNull（查询，null）方法

 起别名，使用关键字 as ...

 因为去了重，又按顺序排序，使用 limit（）方法，查询第二大的数据，即第二高的薪水，即 limit(1,1) （因为默认从0开始，所以第一个1是查询第二大的数，第二个1是表示往后显示多少条数据，这里只需要一条）

 为什么最外层没有加上 from employee？如果没有 ifNull() 函数，单纯只是嵌套SQL，加上 from employee 没有问题；但问题是 select ifNull 是搭配使用的，并不属于嵌套查询

8.1484-按日期分组销售产品

代码如下（示例）:

# Write your MySQL query statement below
select sell_date,
    count(distinct product) num_sold,
    group_concat(distinct product) products
from Activities
group by sell_date
order by sell_date;

9.1327-列出指定时间段内所有的下单产品

代码如下（示例）:

# Write your MySQL query statement below
select product_name, sum(unit)  unit
from Products join Orders using (product_id)
where order_date like "2020-02%"
group by product_name
having unit >= 100;

10.1517-查找拥有有效邮箱的用户

代码如下（示例）:

# Write your MySQL query statement below
select *
from users 
where mail regexp '^[a-zA-A]+[a-zA-Z0-9_\\./\\-]*@leetcode\\.com$'

---

总结

以上是今天要讲的内容，练习了一些SQL题。