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

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

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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);

 

 

 

 

 

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值