leetcode database 题目
来源:互联网 发布:网络小说家排名 编辑:程序博客网 时间:2024/05/16 10:03
1.Second Highest Salary
select ifnull(
(select DISTINCT salary from emplyee order by salary desc limit 1,1),null) as salary
2.Customers Who Never Order
select name from Customers c where id not in (select CustomerId from Orders)
select name as Customers from Customers c LEFT JOIN Orders o on c.id=o.CustomerId where o.CustomerId is null
select name from Customers c where not EXISTS (select CustomerId from Orders o where c.id=o.CustomerId)
3.Duplicate Emails
select Email from
(select Email,count(Email) num from Person GROUP BY Email) p where p.num>1
4.Employees Earning More Than Their Managers
select e.name from Employee e INNER JOIN Employee f where e.ManagerId=f.Id and e.salary>f.salary
5.Combine Two Tables
select p.FirstName,p.LastName,a.City,a.State from Person p left JOIN Address a on p.PersonId=a.PersonId
6.Department Highest Salary
select t.Department,Employee.name Employee,t.salary salary from
(select p.did did,p.dep_name Department,max(p.salary) salary from
(select e.DepartmentID did,d.name dep_name,e.name emp_name,e.salary salary from Employee e,Department d where e.DepartmentId=d.Id) p
GROUP BY p.dep_name)t left JOIN Employee on t.salary=Employee.salary and t.did=Employee.DepartmentId
7.Consecutive Numbers
select DISTINCT num from
(select Num ,
case
when @pre=Num then @count:=@count+1
when (@pre:=Num) is not null then @count:=1
end n
from logs, (select @count := 0) r
order by Id
) a where n>=3
8.Rank Scores
select Score,
case
when @pre=Score then @rank:=@rank
when (@pre:=Score) is not null then @rank:=@rank+1
end Rank
from Scores ,(select @rank:=0,@pre:=-1)r order by Score desc
9.Nth Highest Salary
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select distinct(salary) from Employee order by Salary desc limit N,1
);
END
10.Department Top Three Salaries
select d.Name Department,e.Name Employee,e.salary Salary from Department d,Employee e where e.DepartmentId=d.Id and
(select count(distinct(e1.salary)) from Employee e1 where e1.salary>e.salary and e1.DepartmentId=e.DepartmentId)<3
数据库高级部分:
索引
触发器
函数
游标
存储过程
存储引擎
事务
备份
恢复
- leetcode database 题目
- LeetCode OJ 上 Database 题目记录集
- [Leetcode]database 相关题目的总结
- leetcode DataBase
- Leetcode DataBase
- leetcode题目
- LeetCode 题目
- < 题目 > LeetCode
- leetcode database 183
- [LeetCode][Database]Duplicate Emails
- [LeetCode][Database]Rising Temperature
- [LeetCode][Database]Consecutive Numbers
- [LeetCode][Database]Rank Scores
- 【Leetcode Database】Duplicate Emails
- 【leetcode】 database Rising Temperature
- 【leetcode】 database Duplicate Emails
- Leetcode Database - Oracle Partition
- LeetCode Database Problems
- Unicode字符集和多字节字符集关系
- spring+静态代理
- HDU 3395 Special Fish?费用流(虚拟边)?647★KM(加虚拟边权)
- Go语言之nil详解
- IDF实验室之万里寻踪图片里的秘密
- leetcode database 题目
- Android 异步加载图片,使用LruCache和SD卡或手机缓存,效果非常的流畅
- STL源码剖析—整体简要概述
- AsynImageLoader andriod异步加载图片类
- Vector Clock算法的理解
- database link
- CI项目架构(1)---前后台分离
- 字符串加密
- 机器学习:线性回归 最小二乘法 方差