175. Combine Two Tables && 176. Second Highest Salary

最新推荐文章于 2022-07-01 15:29:14 发布

原创最新推荐文章于 2022-07-01 15:29:14 发布 · 222 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文提供SQL实战案例，包括如何结合两张表以获取人员及其地址信息，以及如何从员工表中选取第二高薪水。通过左连接和子查询技巧，展示SQL在数据整合与高级查询方面的强大能力。

175. Combine Two Tables

SQL Schema

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 FirstName,LastName,City,State from Person left join Address on Person.PersonId=Address.PersonId;

176. Second Highest Salary

SQL Schema

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                 |
+---------------------+

# Write your MySQL query statement below


# select (select distinct Salary   from Employee order by Salary desc limit 1 offset 1) as SecondHighestSalary;

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