前言，主要是为了在实战中记语法，各位如果想刷这些题还是刷过再来看。
题目185.每个部门工资最高的三个人
表结构：
| Id | Name | Salary | DepartmentId |
|---|---|---|---|
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
每个员工有id和部门id
from Department dep, Employee emp
where emp.DepartmentId=dep.Id and
3 > (Select count(distinct Salary)
From Employee
where DepartmentId = dep.Id and Salary>emp.Salary
)
题目175. 将员工信息表和员工工资表并起来，其中有公共行PersonId，工资表没有记录的用null代替。
表结构：
Table: Person
| Column Name | Type |
|---|---|
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
PersonId is the primary key column for this table.
Table: Address
| Column Name | Type |
|---|---|
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
AddressId is the primary key column for this table.
解答：
SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person left join Address on Person.PersonId=Address.PersonId
left join 类似于right join
join 等效于 inner join 只查两边都有的
full join 则是任一方有则加入结果
(摘自评论)The SQL syntax for how to use Where vs. On
- SELECT <field name>
FROM <table name>
WHERE <condition> - SELECT <field name>
FROM <table name 1> JOIN <table name 2>
ON <condition>
题目176: 查找薪水第二高的员工：
表结构：
| Id | Salary |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
select Salary as SecondHighestSalary from Employee order by Salary DESC limit 1 offset 1
先排序，再用select的关键字对结果进行处理，或者 嵌套select
select MAX(Salary) as SecondHighestSalary from Employee
where Salary <(select MAX(Salary) from Employee)
题目181. 工资比经理还高的员工每个员工都有一个经理或为空，找出所有工资比其经理高的员工
表结构：
| Id | Name | Salary | ManagerId |
|---|---|---|---|
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
解法：
select emp.Name as Employee from Employee as emp, Employee as man where emp.ManagerId = man.Id and emp.Salary > man.Salary
分别查两个Employee表从中去所属经理id与本体id相同的进行比较。
题目182. 查找所有重复的邮件
表格如下：
| Id | |
|---|---|
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
count(column_name)返回指定列名中不同种值的种数数目
count(*)返回列表的记录数
解法1：distinct限制结果无重复，嵌套查找一个email在本表中匹配得到第二个，就是具有重复记录的email
/*runtime 50.52%/
select distinct Email from Person as p1
where
(
select count(*) from Person p2
where
p1.Email = p2.Email
)>1;
解法2(优): having直接使用聚合函数，group by也是针对聚合函数就某一列或多列进行分组
Select Email
from person
Group by Email
Having count(*) > 1;
