网站建设会议记录,聚合搜索引擎入口,那里可以免费做网站,设计公司网页欣赏文章目录 主要内容一.SQL练习题1.602-好友申请#xff1a;谁有最多的好友代码如下#xff08;示例#xff09;: 2.585-2016年的投资代码如下#xff08;示例#xff09;: 3.185-部门工资前三高的所有员工代码如下#xff08;示例#xff09;: 4.1667-修复表中的名字代码… 文章目录 主要内容一.SQL练习题1.602-好友申请谁有最多的好友代码如下示例: 2.585-2016年的投资代码如下示例: 3.185-部门工资前三高的所有员工代码如下示例: 4.1667-修复表中的名字代码如下示例: 5.1527-患某种疾病的患者代码如下示例: 6.196-删除重复的电子邮箱代码如下示例: 7.176-第二高的薪水代码如下示例: 8.1484-按日期分组销售产品代码如下示例: 9.1327-列出指定时间段内所有的下单产品代码如下示例: 10.1517-查找拥有有效邮箱的用户代码如下示例: 总结 主要内容
LeetCode-高频SQL50题 41-50
一.SQL练习题 1.602-好友申请谁有最多的好友 代码如下示例:
# Write your MySQL query statement below
select id,count(*) num
from ((select requester_id idfrom requestAccepted)union all(select accepter_id idfrom 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_cityfrom 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.Idfrom Employee as e1 left join Employee as e2on e1.DepartmentId e2.DepartmentId and e1.Salary e2.Salarygroup by e1.Idhaving count(distinct e2.Salary) 2
)
and e.DepartmentId in (select Id from Department)
order by d.Id asc,e.Salary desc4.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题。
