[Leetcode]database 相关题目的总结
来源:互联网 发布:最优化理论与方法 目录 编辑:程序博客网 时间:2024/06/06 00:03
Combine Two Tables
https://leetcode.com/problems/combine-two-tables/
解题思路:
合并table Person 和Address,其中提示说不管Address里有没有personId,Person里的记录都要显示全部。这是典型的 left join 功能。
select P.FirstName, P.LastName, A.City, A.State from Person P left outer join Address A on P.PersonId = A.PersonId
知识点:left join (left outer join) 关键字从左表返回所有的行,即使在右表中没有匹配的行
Second Highest Salary
https://leetcode.com/problems/second-highest-salary/
解题思路:
找到第二大的数字。第一反应就是先找到 max,然后从比max小的数里面找个最大的。
select max(Salary) from Employeewhere Salary <(select max(Salary) from Employee)
知识点:max 聚合函数,不能跟where一起,如果要把聚合函数作为条件,用having
Nth Highest Salary
https://leetcode.com/problems/nth-highest-salary/
解题思路:
写个找第N大数字的函数,先排序再取数字。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN set N = N-1; RETURN ( select distinct Salary from Employee order by Salary desc limit N,1 );END
知识点: limit N,相当于打印前N条; limit N,m, 打印从第N+1开始的m笔记录。计数从0开始;
limit N-1 是语法错误的,这里只能用 numeric
Rank Scores
https://leetcode.com/problems/rank-scores/
解题思路:
对每一个Score,找出>=它的score的数量,这个数量就是rank
select Score ,(select count(*) from (select distinct Score s from Scores) tmp where s >= Score)from Scoresorder by Score desc
知识点:在内层的select中 s >= Score 表示,对一个Score,每一个s都跟它比较一次。
相当于外面的select是外层for,内嵌的select是内层for的感觉
Employees Earning More Than Their Managers
https://leetcode.com/problems/employees-earning-more-than-their-managers/
解题思路:
看成两个表e1和e2,找到满足e1.ManagerId = e2.Id 并且 e1.Salary > e2.Salary的记录
select e1.Name as Employee from Employee e1, Employee e2 where e1.ManagerId = e2.Id and e1.Salary > e2.Salary
Duplicate Emails
https://leetcode.com/problems/duplicate-emails/
解题思路:
找到group以后count > 1的记录
select Email from Person group by Email having count(Email) > 1
知识点:count作为合计函数,无法与where一起使用,所以出现了having
Customers Who Never Order
https://leetcode.com/problems/customers-who-never-order/
解题思路:
先 left join,再找到 CustomerId为空的记录
select C.Name as Customers from Customers C left join Orders O on C.Id = O.CustomerIdwhere O.CustomerId is NULL
Department Highest Salary
https://leetcode.com/problems/department-highest-salary/
解题思路:
合并两个table,然后看salary是否是 max的那个
select D.Name as Department, E.Name as Employee, E.Salary from Department D, Employee Ewhere D.Id = E.DepartmentIdand E.Salary in(Select max(Salary) from Employee where DepartmentId = E.DepartmentId);
知识点: max
Department Top Three Salaries
https://leetcode.com/problems/department-top-three-salaries/
解题思路:
先join,然后再来个select count,计算比当前salary大的数量,0表是当前salary最大,以此类推,找到 count = 0,1,2的就是了
select D.Name as Department, E1.Name as Employee, E1.Salaryfrom Employee E1join Department D on E1.DepartmentId = D.Idwhere 3 > (select count(*) from (select distinct Salary, DepartmentId from Employee) E2 where E2.Salary > E1.Salary and E1.DepartmentId = E2.DepartmentId)
知识点:count(*) , distinct , 3 > (table_numeric_column)
Delete Duplicate Emails
https://leetcode.com/problems/delete-duplicate-emails/
解题思路:
找到以email Group的最小id,然后删除所有不在这个id 集合里面的所有记录
delete from Person where Id not in (select A.Id from (select min(Id) as Id from Person Group by Email) A)
知识点:not in (table column )
Rising Temperature
https://leetcode.com/problems/rising-temperature/
解题思路:
当成2个表来进行比较。条件满足 DATEDIFF(a.Data, b.Data) = 1 并且 a.温度 > b.温度
select a.Id from Weather as a, Weather as b where DATEDIFF(a.Date, b.Date) = 1 and a.Temperature > b.Temperature
知识点:DATEDIFF(a, b) = 1 当a-b=1时;
Trips and Users
https://leetcode.com/problems/trips-and-users/
解题思路:
说不清楚,直接看指令吧
select t.Request_at Day, round(sum(t.Status like "cancelled_%") / count(*), 2) Rate from Trips t join Users u on t.Client_Id = u.Users_Idwhere u.Banned = "No" and t.Request_at >="2013-10-01" and t.Request_at <= "2013-10-03"group by t.Request_at
知识点:
round( , 2), sum(), like, count, group, where
0 0
- [Leetcode]database 相关题目的总结
- leetcode database 题目
- [Leetcode][链表]相关题目汇总/分析/总结
- 【二叉树】常用处理与leetcode相关题目总结
- [Leetcode][二叉树]相关题目汇总/分析/总结
- [LeetCode]Sliding Window Algorithm相关题目总结【重要】
- 算法思想-深度搜索算法-leetcode相关题目总结
- [Leetcode][双指针/多指针]相关题目汇总/分析/总结
- python leetcode相关题目
- C内存相关的题目总结
- LeetCode相关的KSum总结
- LeetCode OJ 上 Database 题目记录集
- 相关比赛题目总结
- leetcode总结 -- 关于tree path的题目
- LeetCode 题目总结
- LeetCode 题目总结/分类
- LeetCode 题目总结/分类
- LeetCode 题目总结/分类
- [LeetCode]Remove Duplicates from Sorted Array
- 图的广度遍历(BFS)与深度遍历(DFS)
- Linux--使用fdisk进行磁盘管理
- fdisk分区工具
- [LeetCode]Climbing Stairs
- [Leetcode]database 相关题目的总结
- 非计算机专业的伟伯是如何拿到阿里Offer的。励志!
- Linux文件系统
- Linux文件系统挂载管理
- DICOM:DICOM3.0标准中文版开源书籍之”本地版本管理方案“
- JAVA 获取文件MINETYPE
- Linux下获取帮助
- Linux权限机制
- [IOS]CoreAnimation动画效果示例