SQL查询练习一(From LeetCode)

小说:大学生干什么能挣钱作者:秉宗更新时间:2019-03-18字数:89508

SQL查询练习一(From LeetCode)


 请选用MySQL进行测试.

1.找出后一天比前一天温度高的日期(E)

思路:将该表进行自关联,然后选择出日期靠后的一天,最后选择温度高的日期

1 select Weather.Id
2 from Weather join Weather w
3 on datediff(Weather.Date,w.Date) = 1
4 and Weather.Temperature > w.Temperature
View Code

2.找出人口在3百万或者领土面积在25百万的国家(E)

 

思路:使用where进行条件过筛选,or进行或判断

1 select name,population,area
2 from World
3 where area > 3000000 or population > 25000000
View Code
1 1 select name,population,area
2 2 from World
3 3 where area > 3000000 or population > 2500000

3.判断是否是三角形(E)

思路,使用case when进行搭配,使用三角形定义进行判断x+y>z,x+z>y,y+z>x

1 select x,y,z,
2 case
3     when x+y>z and y+z>x and x+z>y then "Yes"
4     else "No"
5 end as triangle 
6 from triangle 
View Code

 4.找出薪水第二高的员工

思路:先找出最多的薪水的员工,在把他的薪水小于最大的工资即可

1 select Max(Salary) as SecondHighestSalary 
2 from Employee 
3 where Salary < (select Max(Salary) from Employee)
View Code

5.找出每个学科都有多少名学生(M)

思路:将两张表进行左连接,一department表作为主表,然后按照dept_name进行分组,最后按照人数进行降序排列

1 select d.dept_name,count(student_id) as student_number
2 from department d left join Student s
3 on d.dept_id = s.dept_id
4 group by d.dept_name
5 order by student_number desc,d.dept_name
View Code

6.找出每个部门薪水最高的员工(M)

思路:将两张表进行连接,内层查询根据department表的name进行分组,每组的最大值,既是每个部门的薪水最大值,然后传递给外层的部门id和薪水即可

1 select d.Name as Department,e.Name as Employee,e.Salary as Salary
2 from Department d join Employee e
3 on e.DepartmentId = d.Id
4 where (e.DepartmentId,e.Salary) in
5 (
6     select DepartmentId,max(Salary)
7     from Employee
8     group by DepartmentId
9 )
View Code

7.找出至少有5名下属的领导(M)

思路:使用内层查询查找出有5名下属的ManagerId然后,将外层查询的员工Id=ManagerId就是查询的结果

1 select e1.Name
2 from Employee e1
3 join
4 (
5     select ManagerId from Employee
6     group by ManagerId
7     having count(*) >= 5
8 ) as e2
9 on e1.Id = e2.ManagerId
View Code

8.找出得票最多的候选人(M)

 

思路:先在内层查询中找出最受欢迎的候选人,然后将中间表的候选人Id既是赢家的候选人id,两者相等即可

 1 select c.Name
 2 from Candidate c
 3 join 
 4 (
 5     select CandidateId  from Vote
 6     group by CandidateId  
 7     order by count(*) desc
 8     limit 1
 9 ) as winner
10 on c.id = winner.CandidateId;
View Code

9.根据Score计算等级(M)

思路:将两张表进行自连接,根据Id进行分组,最后根据Rank进行排序

1 select s.Score,count(distinct t.Score) as Rank
2 from Scores s join Scores t
3 on s.Score <= t.Score
4 group by s.Id
5 order by Rank 
View Code

10.找出二叉树的节点分布(M)

思路:使用case when的结构进行循环判断输出

 1 select id,
 2 case
 3     when tree.id = (select atree.id from tree atree where atree.p_id is NULL)
 4         then "Root"
 5     when tree.id in (select atree.p_id from tree atree)
 6         then "Inner"
 7     else 
 8         "Leaf"
 9 end as Type
10 from tree
11 order by id
View Code

11.找出每个部门薪水排前三名的员工(H)

 

思路:先进行表连接,将内层查询的结果和外部的表的Salary相比较,选择前面3个

 1 select d.Name as Department,e.Name as Employee,e.Salary
 2 from Employee e join Department d
 3 on e.DepartmentId = d.Id
 4 where 3 >
 5 (
 6     select count(distinct e2.Salary)
 7     from Employee e2
 8     where e2.Salary > e.Salary
 9     and e.DepartmentId = e2.DepartmentId
10 )
View Code

12.找出2013-10-01到2013-10-03之间的网约车的取消率(H)

思路:计算取消率,使用case when语法,找出Trips中Status变量以canceled_开头的比例

1 select t.Request_at as Day,
2 round(sum(case when t.Status like "cancelled_%" then 1 else 0 end)/count(*),2) as "Cancellation Rate"
3 from Trips t join Users u
4 on t.Client_Id = u.Users_Id  
5 where u.Banned = "No"
6 and t.Request_at between "2013-10-01" and "2013-10-03"
7 group by t.Request_at
View Code

13.找出每个部门员工薪水的中位数(H)

 

思路:将此表进行自关联,计算工资的中位数,使用case when计算中间表的中位数

1 select e.Id,e.Company,e.Salary
2 from Employee e join Employee aliens
3 on e.Company = aliens.Company
4 group by e.Company,e.Salary
5 having sum(case when e.Salary = aliens.Salary then 1 else 0 end) >=
6 abs(sum(sign(e.Salary-aliens.Salary)))
7 order by e.Id
View Code

 

当前文章:http://zxqss.com/content/03-14/81030/content_5683481642.html

发布时间:2019-03-18 07:09:14

哔哩哔哩投稿赚钱吗 乞丐微信乞讨 宁县农村治富项目 2013年4月5日渔船遇袭事件 西湖区周边有晚班工作吗? 10_10_闲来无事,在家 零钱通没收益 最赚钱的职业 大宗商品电子交易佣金 内购优惠群

一天能赚三十元的软件 网购分享赚钱的软件 百度公众号怎么赚钱 手机签到投资赚钱软件 手机兼职赚钱平台软件 手机如何做淘客赚钱 T39平台 为为什么用返让 拼多多联盟在哪里 下班想找点兼职工作 2018小本创业冷门项目 50岁女人在家创业项目 白天兼职工作工资日结 打装备赚钱的手游 简单的挣钱方法大全 开通淘宝客推广的条件 玩转联盟赚钱可靠吗 线上网络兼职可靠吗 在家上班的创业项目

编辑:海邓陵

我要说两句: (0人参与)

发布