点击刷新
请选用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
2.找出人口在3百万或者领土面积在25百万的国家(E)
思路:使用where进行条件过筛选,or进行或判断
1 select name,population,area 2 from World 3 where area > 3000000 or population > 25000000
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
4.找出薪水第二高的员工
思路:先找出最多的薪水的员工,在把他的薪水小于最大的工资即可
1 select Max(Salary) as SecondHighestSalary 2 from Employee 3 where Salary < (select Max(Salary) from Employee)
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
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 )
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
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;
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
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
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 )
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
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
© 著作权归作者所有
相关热门文章
发表评论
