欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

辣鸡刘的Leetcode之旅3(SQL Part)【两表合并查询,第二高薪,挣钱比经理多, 重复值查询】

程序员文章站 2022-03-09 10:33:24
...

1. 两表合并查询(Combine Two Tables)

题目描述:

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.
Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:
FirstName, LastName, City, State

这个还是挺简单的,直接上:

select Person.FirstName,Person.LastName,Address.City,Address.State from Person left join Address on Person.PersonId= Address.PersonId;

描述:
SQL中常见的JOIN

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

上图:
辣鸡刘的Leetcode之旅3(SQL Part)【两表合并查询,第二高薪,挣钱比经理多, 重复值查询】

2. 第二高薪(Second Highest Salary)

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

找出第二高的,也就是在除去最高的剩余里面找出最高的;

select max(Salary) as SecondHighestSalary from Employee where  Salary not in (select max(Salary) from Employee);

3. 挣钱比经理多(Employees Earning More Than Their Managers)

题目描述:

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

对于本题,Joe的经理是编号为3的Sam,Henry的经理为编号为4的Max,按照题目要求,Joe的工资是高于他的经理Sam的(7K>6K),所以本例的目的是返回Joe:
类似于两表查询:

select name as Employee from Employee e1 where Salary > (select Salary from Employee e2 where e1.ManagerId=e2.Id) 

或者:

select name as Employee from Employee e1 
where exists (select * from Employee e2 where e2.Id=e1.ManagerId and e1.Salary>e2.Salary )
建议写exists语句时,子查询中直接用*,而不用对列进行任何函数操作,避免碰到官方bug,

4. 重复值查询

题目描述:

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | aaa@qq.com |
| 2  | aaa@qq.com |
| 3  | aaa@qq.com |
+----+---------+
For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| aaa@qq.com |
+---------+
Note: All emails are in lowercase.

这个比较简单:

Select Email from Person group by Email having count(Email)>1;

这里注意三点:

  1. group by:常规用法是配合聚合函数,利用分组信息进行统计,常见的是配合max等聚合函数筛选数据后分析,以及配合having进行筛选后过滤。
  2. having:having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。
  3. count():它返回检索行的数目, 不论其是否包含 NULL值。
相关标签: SQL