请选用 MySQL 进行测试.
1. 找出后一天比前一天温度高的日期 (E)
思路: 将该表进行自关联, 然后选择出日期靠后的一天, 最后选择温度高的日期
- select Weather.Id
- from Weather join Weather w
- on datediff(Weather.Date,w.Date) = 1
- and Weather.Temperature > w.Temperature
- View Code
2. 找出人口在 3 百万或者领土面积在 25 百万的国家 (E)
思路: 使用 where 进行条件过筛选, or 进行或判断
- select name,population,area
- from World
- where area > 3000000 or population > 25000000
- View Code
- select name,population,area
- from World
- where area > 3000000 or population > 2500000
3. 判断是否是三角形 (E)
思路, 使用 case when 进行搭配, 使用三角形定义进行判断 x+y>z,x+z>y,y+z>x
- select x,y,z,
- case
- when x+y>z and y+z>x and x+z>y then 'Yes'
- else 'No'
- end as triangle
- from triangle
- View Code
4. 找出薪水第二高的员工
思路: 先找出最多的薪水的员工, 在把他的薪水小于最大的工资即可
- select Max(Salary) as SecondHighestSalary
- from Employee
- where Salary < (select Max(Salary) from Employee)
- View Code
5. 找出每个学科都有多少名学生 (M)
思路: 将两张表进行左连接, 一 department 表作为主表, 然后按照 dept_name 进行分组, 最后按照人数进行降序排列
- select d.dept_name,count(student_id) as student_number
- from department d left join Student s
- on d.dept_id = s.dept_id
- group by d.dept_name
- order by student_number desc,d.dept_name
- View Code
6. 找出每个部门薪水最高的员工 (M)
思路: 将两张表进行连接, 内层查询根据 department 表的 name 进行分组, 每组的最大值, 既是每个部门的薪水最大值, 然后传递给外层的部门 id 和薪水即可
- select d.Name as Department,e.Name as Employee,e.Salary as Salary
- from Department d join Employee e
- on e.DepartmentId = d.Id
- where (e.DepartmentId,e.Salary) in
- (
- select DepartmentId,max(Salary)
- from Employee
- group by DepartmentId
- )
- View Code
7. 找出至少有 5 名下属的领导 (M)
思路: 使用内层查询查找出有 5 名下属的 ManagerId 然后, 将外层查询的员工 Id=ManagerId 就是查询的结果
- select e1.Name
- from Employee e1
- join
- (
- select ManagerId from Employee
- group by ManagerId
- having count(*) >= 5
- ) as e2
- on e1.Id = e2.ManagerId
- View Code
8. 找出得票最多的候选人 (M)
思路: 先在内层查询中找出最受欢迎的候选人, 然后将中间表的候选人 Id 既是赢家的候选人 id, 两者相等即可
- select c.Name
- from Candidate c
- join
- (
- select CandidateId from Vote
- group by CandidateId
- order by count(*) desc
- limit 1
- ) as winner
- on c.id = winner.CandidateId;
- View Code
9. 根据 Score 计算等级 (M)
思路: 将两张表进行自连接, 根据 Id 进行分组, 最后根据 Rank 进行排序
- select s.Score,count(distinct t.Score) as Rank
- from Scores s join Scores t
- on s.Score <= t.Score
- group by s.Id
- order by Rank
- View Code
10. 找出二叉树的节点分布 (M)
思路: 使用 case when 的结构进行循环判断输出
- select id,
- case
- when tree.id = (select atree.id from tree atree where atree.p_id is NULL)
- then 'Root'
- when tree.id in (select atree.p_id from tree atree)
- then 'Inner'
- else
- 'Leaf'
- end as Type
- from tree
- order by id
- View Code
11. 找出每个部门薪水排前三名的员工 (H)
思路: 先进行表连接, 将内层查询的结果和外部的表的 Salary 相比较, 选择前面 3 个
- select d.Name as Department,e.Name as Employee,e.Salary
- from Employee e join Department d
- on e.DepartmentId = d.Id
- where 3 >
- (
- select count(distinct e2.Salary)
- from Employee e2
- where e2.Salary > e.Salary
- and e.DepartmentId = e2.DepartmentId
- )
- View Code
12. 找出 2013-10-01 到 2013-10-03 之间的网约车的取消率 (H)
思路: 计算取消率, 使用 case when 语法, 找出 Trips 中 Status 变量以 canceled_开头的比例
- select t.Request_at as Day,
- round(sum(case when t.Status like 'cancelled_%' then 1 else 0 end)/count(*),2) as "Cancellation Rate"
- from Trips t join Users u
- on t.Client_Id = u.Users_Id
- where u.Banned = 'No'
- and t.Request_at between '2013-10-01' and '2013-10-03'
- group by t.Request_at
- View Code
13. 找出每个部门员工薪水的中位数 (H)
思路: 将此表进行自关联, 计算工资的中位数, 使用 case when 计算中间表的中位数
- select e.Id,e.Company,e.Salary
- from Employee e join Employee aliens
- on e.Company = aliens.Company
- group by e.Company,e.Salary
- having sum(case when e.Salary = aliens.Salary then 1 else 0 end) >=
- abs(sum(sign(e.Salary-aliens.Salary)))
- order by e.Id
- View Code
来源: https://www.cnblogs.com/luhuajun/p/8471107.html