175. 组合两个表

(主键分别为:personId、addressId) 编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

由题设可知本题需要用链接查询，无论是否存在相对应的地址信息都必须输出person，所以是person外连接address。

select FirstName,LastName,City,State
from    Person left outer join Address
on Person.personId = Address.personId

176. 第二高的薪水

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null 。

解法一：由题设知，本题运用排序查询，降序取第二行的数值；若不存在，返回null应该多嵌套一层select语句。
若出现两个一模一样的最大值，则第二大值并不会出现再第二行，所以查询时应当去重。

select (select distinct salary 
from Employee
order by salary desc limit 1,1) as SecondHighestSalary

解法二：运用max函数，查找不是最大值的最大值

select max(Salary) SecondHighestSalary
from Employee
where Salary != (select max(Salary) from Employee)

177. 第N高的薪水

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

在函数中运用176的解法一
因为在limit语句中不能进行运算，所以要先将N设置为N-1。

CREAT FUNCTION getNthHighestSalary(N int) RETURNS int
BEGIN

    Set N = N - 1;

  RETURN(select distinct Salary from Employee order by Salary desc limit N,1);

End

178. 分数排名

编写 SQL 查询对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

分数从高到底排列(降序)：order by desc
制作排名：把一号位的数据拿出来，与所有数据(去重)作比较，挑选出大于等于该数据，由这些数据组成的表的长度就是排名

对所有数据进行这样的操作：group by id(对键值group by 可以进行遍历)

select s1.score,count(distinct(s2.score)) as 'rank'
from Scores s1,Scores s2
where s1.score <= s2.score
group by s1.id 
order by s1.score desc

180. 连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。返回的结果表中的数据可以按任意顺序排列。

因为需要查找三次连续出现的数字，所以创建3个Logs表用id和num模拟判断条件即可。

select distinct L1.num as ConsecutiveNums
from
  Logs L1,
  Logs L2,
  Logs L3
where
  L1.id = L2.id - 1
  and L2.id = L3.id - 1
  and L1.num = L2.num
  and L2.num = L3.num

181. 超过经理收入的员工

编写一个SQL查询来查找收入比经理高的员工。以任意顺序返回结果表。

把员工和其对应的经理的薪水做比较。

select e.name as Employee
from Employee e, Employee m
where e.managerId = m.id
  and e.salary > m.salary

182. 查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

与上一题类似，把比大小换成是否相等

select distinct a.Email as Email
from Person a, Person b
where a.id != b.id
and a.Email = b.Email

183. 从不订购的客户

编写一个 SQL 查询，找出所有从不订购任何东西的客户。

select name as Customers
from Customers left join Orders
on Customers.id = Orders.CustomerId
where CustomerId is Null

184. 部门工资最高的员工

编写SQL查询以查找每个部门中薪资最高的员工。

分别找出每个部门中工资最高的金额形成一个表。
然后比对员工表中的部门信息和薪资在形成的新表中是否一致。
将一致的信息输出。

select Department.name as Department,Employee.name as Employee, salary
from Employee left join Department.id
on Employee.Departmentid = Department.id
where (Departmentid,salary) in (select Departmentid,Max(salary) 
                                from Employee 
                                group by Departmentid)

185. 部门工资前三高的所有员工

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的高收入者是指一个员工的工资在该部门的不同工资中排名前三。编写一个SQL查询，找出每个部门中收入高的员工。

对于这种分组内取前几名的问题，可以先group by然后用having count来筛选。
比如这题，找每个部门的工资前三名，那么先在子查询中用Employee和自己做连接，连接条件是【部门相同但是工资比我高】，那么接下来按照having count(Salary) <= 2来筛选。原理是：如果【跟我一个部门而且工资比我高的人数】不超过2个，那么我一定是部门工资前三，这样内层查询可以查询出所有符合要求的员工ID，接下来外层查询就简单了。

select Department.name as Department,Employee.name as Employee,salary
from Employee left join Department
on Employee.Departmentid = Department.id
where Employee.id in
(
    select e1.id 
    from Employee e1 left join Employee e2
    on e1.Departmentid = e2.Departmentid and e1.salary < e2.salary
    group by e1.id
    having count(distinct(e2.salary)) <=2   
)

196. 删除重复的电子邮箱

编写一个SQL查询来删除所有重复的电子邮件，只保留一个id最小的唯一电子邮件。

Delete p from Person p,Person e
where p.id > e.id and p.email = e.email

197. 上升的温度

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

datediff(日期1, 日期2)：得到的结果是日期1与日期2相差的天数。如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。

select a.id
from Weather a,Weather b
where a.Temperature > b.Temperature
and datediff(a.recordDate,b.recordDate) = 1

595. 大的国家

如果一个国家满足下述两个条件之一，则认为该国是大国：

面积至少为 300 平方公里（即，3000000 km2），或者
人口至少为 2500 万（即 25000000）

编写一个 SQL 查询以报告大国的国家名称、人口和面积。

select name,population,area
from World
where area>=3000000 or population>=25000000

596. 超过5名学生的课

编写一个SQL查询来报告至少有5个学生的所有Class。

select distinct class
from Courses
group by class
having count(distinct(student)) >= 5

620. 有趣的电影

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个LED显示板做电影推荐，上面公布着影评和相关电影描述。作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非boring (不无聊)的并且 id 为奇数的影片，结果请按等级rating排列。

select * 
from cinema
where id%2 = 1
and description != 'boring'    
order by rating desc

leetcode刷题

数据库