前言，主要是為了在實(shí)戰(zhàn)中記語(yǔ)法渺尘，各位如果想刷這些題還是刷過(guò)再來(lái)看悬钳。
題目185.每個(gè)部門(mén)工資最高的三個(gè)人
表結(jié)構(gòu)：
| 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 |
每個(gè)員工有id和部門(mén)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. 將員工信息表和員工工資表并起來(lái)骚露，其中有公共行PersonId趾代，工資表沒(méi)有記錄的用null代替。
表結(jié)構(gòu)：
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 則是任一方有則加入結(jié)果
(摘自評(píng)論)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: 查找薪水第二高的員工：
表結(jié)構(gòu)：
| Id | Salary |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
select Salary as SecondHighestSalary from Employee order by Salary DESC limit 1 offset 1
先排序伴奥，再用select的關(guān)鍵字對(duì)結(jié)果進(jìn)行處理写烤，或者 嵌套select
select MAX(Salary) as SecondHighestSalary from Employee
where Salary <(select MAX(Salary) from Employee)
題目181. 工資比經(jīng)理還高的員工每個(gè)員工都有一個(gè)經(jīng)理或?yàn)榭眨页鏊泄べY比其經(jīng)理高的員工
表結(jié)構(gòu)：
| 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
分別查兩個(gè)Employee表從中去所屬經(jīng)理id與本體id相同的進(jìn)行比較拾徙。
題目182. 查找所有重復(fù)的郵件
表格如下：
| Id | |
|---|---|
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
count(column_name)返回指定列名中不同種值的種數(shù)數(shù)目
count(*)返回列表的記錄數(shù)
解法1：distinct限制結(jié)果無(wú)重復(fù)洲炊，嵌套查找一個(gè)email在本表中匹配得到第二個(gè)，就是具有重復(fù)記錄的email
/*runtime 50.52%/
select distinct Email from Person as p1
where
(
select count(*) from Person p2
where
p1.Email = p2.Email
)>1;
解法2(優(yōu)): having直接使用聚合函數(shù)尼啡，group by也是針對(duì)聚合函數(shù)就某一列或多列進(jìn)行分組
Select Email
from person
Group by Email
Having count(*) > 1;
