个人中心
退出
SQL 案例分析之月度销量冠军
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
问题描述
Oracle
MySQL
Microsoft SQL Server
PostgreSQL
SQLite
今天给大家介绍一个 SQL 实战案例：通过横向子查询（LATERAL subquery）实现销量冠军分析。文章描述了 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 中的实现方法。
问题描述
公司的销售人员负责各种产品的销售，emp_sales 表中记录了每个销售人员每个月份的销量数据。以下是该表中的一些示例数据：
SELECT * FROM emp_sales;
emp_id|sale_year|sale_month|amount |
------|---------|----------|--------|
19| 2021| 1|15672.53|
20| 2021| 1|11160.46|
21| 2021| 1|13763.75|
22| 2021| 1|11210.34|
23| 2021| 1|14610.88|
24| 2021| 1|13747.64|
25| 2021| 1|12816.20|
19| 2021| 2|14413.77|
20| 2021| 2|14266.04|
21| 2021| 2|16984.42|
...
emp_sales 表中的字段分别表示员工编号、销售年份、销售月份以及销售金额。
现在我们想要知道每个月份的销售冠军，也就是每个月份销售金额最高的员工。请问应该如何使用 SQL 查询实现？
从原理上来说，我们可以分别通过查询找出每个月份销量最高的员工。例如 2021 年 1 月份的销量冠军可以使用一下查询语句：
-- MySQL、PostgreSQL 以及 SQLite
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM emp_sales s
JOIN employee e ON (e.emp_id = s.emp_id)
WHERE s.sale_year = 2021
AND s.sale_month = 1
ORDER BY s.amount DESC
LIMIT 1;
-- Oracle 和 SQL Server
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM emp_sales s
JOIN employee e ON (e.emp_id = s.emp_id)
WHERE s.sale_year = 2021
AND s.sale_month = 1
ORDER BY s.amount DESC
offset 0 ROWS
FETCH FIRST 1 ROWS ONLY;
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|庞统 |15672.53|
然后使用同样的方法获取其他月份分销量冠军。
显然，这种方法不够智能，我们需要一种查询语句可以为每个月份返回一个最高销量的员工。下面我们就来介绍如何通过 SQL 横向子查询实现这一功能。
Oracle
Oracle 提供了横向子查询，子查询可以使用 FROM 子句中出现在它之前的表或者查询结果中的字段。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查询 d 的作用是获取销售数据中的所有年度和月份信息。LATERAL 关键字表示横向子查询，子查询 s 通过使用左侧查询结果中的年度和月份数据返回了每个月销售金额最高的员工和相应的金额，最后连接员工表获得员工的姓名。查询返回的结果如下。
SALE_YEAR|SALE_MONTH|EMP_NAME|AMOUNT |
---------|----------|--------|--------|
2021| 1|庞统 |15672.53|
2021| 2|黄权 |16984.42|
2021| 3|邓芝 |16377.44|
2021| 4|简雍 |18744.78|
2021| 5|蒋琬 |19466.56|
2021| 6|庞统 |20154.83|
除了 LATERAL 关键字之外，Oracle 还提供了另一种实现横向子查询功能的 CROSS APPLY 语法。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS APPLY (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
该语句和上面的示例等价，返回的结果也相同。
📝关于 Oracle 横向子查询和 CROSS APPLY 以及 OUTER APPLY 子句的详细信息可以参考官方文档。
另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。
MySQL
MySQL 8.0 提供了横向子查询，子查询可以使用 FROM 子句中出现在它之前的表或者查询结果中的字段。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
LIMIT 1) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|庞统 |15672.53|
2021| 2|黄权 |16984.42|
2021| 3|邓芝 |16377.44|
2021| 4|简雍 |18744.78|
2021| 5|蒋琬 |19466.56|
2021| 6|庞统 |20154.83|
其中子查询 d 的作用是获取销售数据中的所有年度和月份信息。LATERAL 关键字表示横向子查询，子查询 s 可以使用 FROM 子句中出现在它之前的表或者查询结果中的字段。
📝关于 MySQL 横向子查询的详细信息，可以参考官方文档。
另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。
Microsoft SQL Server
Microsoft SQL Server 没有实现 LATERAL 关键字，但是可以使用和 Oracle 相同的 CROSS APPLY 子句实现横向子查询语句。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month
FROM emp_sales) d
CROSS APPLY (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查询 d 的作用是获取销售数据中的所有年度和月份信息。CROSS APPLY 关键字表示子查询 s 通过使用左侧查询结果中的年度和月份数据返回了每个月销售金额最高的员工和相应的金额。以上查询通过使用左侧查询结果（d）中的年度和月份数据返回了每个月销售金额最高的员工和相应的金额：
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|庞统 |15672.53|
2021| 2|黄权 |16984.42|
2021| 3|邓芝 |16377.44|
2021| 4|简雍 |18744.78|
2021| 5|蒋琬 |19466.56|
2021| 6|庞统 |20154.83|
📝关于 Microsoft SQL Server 中的 CROSS APPLY 和 OUTER APPLY 子句的详细介绍，可以参考官方文档。
另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。
PostgreSQL
PostgreSQL 实现了横向子查询，子查询可以使用 FROM 子句中出现在它之前的表或者查询结果中的字段。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
LIMIT 1) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查询 d 的作用是获取销售数据中的所有年度和月份信息，横向子查询 s 通过使用左侧查询结果中的年度和月份数据返回了每个月销售金额最高的员工和相应的金额，最后连接员工表获得员工的姓名。查询返回的结果如下。
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|庞统 |15672.53|
2021| 2|黄权 |16984.42|
2021| 3|邓芝 |16377.44|
2021| 4|简雍 |18744.78|
2021| 5|蒋琬 |19466.56|
2021| 6|庞统 |20154.83|
📝关于 PostgreSQL 横向子查询的详细介绍，可以参考官方文档。
另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。
SQLite
SQLite 目前没有提供横向子查询，因此不能通过这种方式返回月度销量冠军。我们可以利用窗口函数实现同样的功能，例如：
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM (SELECT emp_id, sale_year, sale_month, amount,
RANK() OVER (PARTITION BY sale_year, sale_month ORDER BY amount desc) AS rk
FROM emp_sales) s
JOIN employee e
ON (e.emp_id = s.emp_id)
WHERE rk = 1
ORDER BY s.sale_year, s.sale_month;
窗口函数 RANK 返回了按照月份分组后的销量排名，然后我们返回了排名第一的数据，也就是月度销量冠军。
以上窗口函数的实现语法也适用于其他数据库，具体介绍可以参考这篇文章。
总结
本文通过一个月度销量冠军分析的案例，介绍了如何利用横向子查询引用子查询左侧表中的字段。另外，我们还给出了窗口函数实现相同功能的语法。
分类导航