如何在SQL中使用GROUP BY和ORDER BY

教程

SQL

介绍

结构化查询语言（SQL）数据库可以跨多个表存储和管理大量数据。对于大数据集，了解如何对数据进行排序非常重要，特别是用于分析结果集或为报告或外部通信组织数据。

SQL中的两个常见语句可以帮助您对数据进行排序，分别是GROUP BY和ORDER BY。 GROUP BY语句根据您在查询中指定的列对数据进行分组排序，并与聚合函数一起使用。 ORDER BY允许您按字母顺序或数字顺序以升序或降序方式组织结果集。

在本教程中，您将使用GROUP BY和ORDER BY语句对SQL中的查询结果进行排序。您还将练习在查询中实现聚合函数和WHERE子句，以进一步对结果进行排序。

先决条件

要按照本指南操作，您需要运行某种使用 SQL 的关系型数据库管理系统（RDBMS）的计算机。本教程中的说明和示例是在以下环境中验证的：

A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
在服务器上安装并保障了 MySQL。请按照我们的在Ubuntu 20.04上安装MySQL的方法进行设置。本指南假定您还设置了一个非根 MySQL 用户，如本指南的 第3步 所述。

注意: 请注意，许多关系型数据库管理系统使用其自己独特的 SQL 实现。虽然本教程中概述的命令在大多数 RDBMS 上都能工作，但如果您在 MySQL 之外的系统上测试它们，确切的语法或输出可能会有所不同。

为了练习本教程中的数据结果排序，您需要一个装有示例数据的数据库和表。如果您还没有准备好要插入的数据，请阅读以下 连接到MySQL并设置一个示例数据库 部分，以了解如何创建数据库和表。本教程将在后文中引用这个示例数据库和表。

由于您参与了几种不同类型的电影，您对知道这些电影被电影观众接受程度很感兴趣。具体来说，您想知道每种电影类型的平均观影人数。使用SELECT从movie_genre列中检索各种类型的电影。然后在guest_total列上应用聚合函数AVG，使用AS为名为average的列创建别名，并包括GROUP BY语句以按movie_genre对结果进行分组。以这种方式对它们进行分组将为您提供每种电影类型的平均结果：

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

此输出提供了 movie_genre 组内每种类型的四个平均值。根据这些信息，Action 电影吸引了每场放映的最高平均观众人数。

接下来，假设您想要测量剧院在两个不同日期的收入。以下查询从 date 列返回值，以及由 SUM 聚合函数返回的值。具体而言，聚合函数 SUM 将在括号中封装一个数学方程（使用 * 运算符）将总观众人数乘以票价，表示为：SUM(guest_total * ticket_cost)。此查询使用 AS 子句为聚合函数返回的列提供别名 total_revenue。然后，通过 GROUP BY 语句按 date 列对查询结果进行分组：

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

由于您使用 GROUP BY 对 date 列进行了分组，因此您的输出提供了每天票务销售总收入的结果，本例中，5月27日星期五为$7,272，5月28日星期六为$9,646。

现在想象一下，你想专注于分析一部电影：《坏人们》。在这种情况下，你想弄清楚时间和价格点如何影响家庭选择观看动画电影。对于这个查询，使用聚合函数MAX来检索最大的ticket_cost，确保使用AS为price_data列创建别名。然后，使用WHERE子句将结果按movie_name缩小为仅“坏人们”，并使用AND来基于guest_total大于100的数字使用比较运算符>来确定最受欢迎的电影时间。然后，使用GROUP BY语句并按time进行分组：

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

根据这个输出，在早上9:00的早场时间，更多的客人观看了《坏人们》电影，票价更实惠，为每张票8.00美元。然而，这些结果也显示，电影客人在下午5:00支付了更高的票价，为每张票13.00美元，这表明家庭更喜欢不太晚的放映时间，并愿意为票价支付更多。与晚上10:00播放时间相比，当时《坏人们》电影只有83位客人，每张票的价格是18.00美元。这些信息对于向电影院经理提供证据，证明增加更多的早场和早晚时间段可以增加家庭观众的出席率是有帮助的，因为他们根据所偏好的时间和价格点做出选择。

请注意，即使 GROUP BY 几乎总是与聚合函数一起使用，但也可能存在例外情况，虽然可能性很小。但是，如果您确实想要在不使用聚合函数的情况下对结果进行分组，可以使用 DISTINCT 语句来实现相同的结果。 DISTINCT 子句通过返回列中的唯一值来移除结果集中的任何重复项，它只能与 SELECT 语句一起使用。例如，如果您想要按名称将所有电影分组在一起，可以使用以下查询：

SELECT DISTINCT movie_name FROM movie_theater;

Output+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

正如您回忆起在表中查看所有数据时，由于有多个放映，电影名称存在重复。因此，DISTINCT 移除了这些重复项，并有效地将唯一值分组在单个列 movie_name 下。这与包含 GROUP BY 语句的以下查询实际上是相同的：

SELECT movie_name FROM movie_theater GROUP BY movie_name;

现在您已经练习了如何使用带有聚合函数的 GROUP BY，接下来您将学习如何使用 ORDER BY 语句对查询结果进行排序。

使用 `ORDER BY`

ORDER BY语句的功能是根据查询中指定的列对结果进行升序或降序排序。根据其后指定的列存储的数据类型，ORDER BY将按字母顺序或数字顺序组织它们。默认情况下，ORDER BY将以升序对结果进行排序；但是，如果您希望以降序排序，必须在查询中包含关键字DESC。您还可以将ORDER BY语句与GROUP BY一起使用，但必须在其后才能正常使用。与GROUP BY类似，ORDER BY也必须在FROM语句和WHERE子句之后。使用ORDER BY的一般语法如下：

ORDER BY syntax

SELECT column_1, column_2 FROM table ORDER BY column_1;

让我们继续使用电影院的样本数据，并练习使用ORDER BY对结果进行排序。从以下查询开始，该查询检索guest_total列中的值，并使用ORDER BY语句对这些数值进行排序：

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

由于您的查询指定了一个包含数值的列，ORDER BY语句按照数字和升序对结果进行了排序，从guest_total列下的25开始。

如果您希望按降序排序列，您可以在查询末尾添加DESC关键字。此外，如果您想按movie_name下的字符值对数据进行排序，您可以在查询中指定。让我们执行这种类型的查询，使用ORDER BY按movie_name列中的字符值按降序排序。进一步排序结果，通过包含WHERE子句来检索time列中 10:00 pm 上映的电影数据：

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

此结果集按字母顺序降序列出了四部不同的电影，从《壮志凌云：疾速追击》到《唐顿庄园：新时代》为止。

对于下一个查询，请将ORDER BY和GROUP BY语句与聚合函数SUM组合，生成每部电影的总收入结果。但是，假设电影院错误地统计了总来宾数量，并忘记了包括预购和预订了每场放映的12人团体票的特殊聚会。

在此查询中使用 SUM 并通过实现加法操作符 + 包含每场电影展示的额外12位客人，然后将 guest_total 加上 12。确保将此内容括在括号中。然后，将此总数乘以 ticket_cost 使用乘法操作符 *，并通过在末尾关闭括号完成数学方程。添加 AS 子句以为新列创建别名 total_revenue。然后，使用 GROUP BY 根据从 movie_name 列检索的数据对每部电影的 total_revenue 结果进行分组。最后，使用 ORDER BY 按升序组织新列 total_revenue 下的结果：

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

此结果集告诉我们每部电影的总收入，包括额外的12位客人票销售，并按照从最低到最高的升序组织总票销售。从中，我们了解到《壮志凌云：疾速追击》获得了最多的票房销售，而《男人》获得了最少的票房销售。与此同时，《坏人》和《唐顿庄园：新时代》电影的总票房销售非常接近。

在这一部分，您练习了实现 ORDER BY 语句的各种方式以及如何指定您喜欢的顺序，例如字符和数字数据值的升序和降序。您还学习了如何使用 WHERE 子句来缩小结果范围，并执行了一个使用 GROUP BY 和 ORDER BY 语句以及聚合函数和数学方程的查询。

结论

了解如何使用 GROUP BY 和 ORDER BY 语句对结果和数据进行排序是重要的。无论您是想将多个结果组织到一个组中，还是按照字母顺序和降序组织其中的一个列，或者同时执行两者；这都取决于您和您期望的结果。您还了解了使用 WHERE 子句进一步排序结果的其他方法。如果您想了解更多，请查看我们关于如何在SQL中使用通配符的教程，以练习使用 LIKE 子句过滤结果。

Source:
https://www.digitalocean.com/community/tutorials/how-to-use-groupby-and-orderby-in-sql

介绍

先决条件

连接到MySQL并设置一个示例数据库

使用 GROUP BY

使用 ORDER BY

结论

使用 `GROUP BY`

使用 `ORDER BY`