如何使用SQL系列 之 如何在SQL中使用GROUP BY和ORDER BY_sqlorderby和groupby一起用

引言

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

SQL中有两个常用的用于数据排序的语句:GROUP BY和ORDER BY。GROUP BY语句根据查询中指定的列对数据进行分组，并与聚集函数一起使用。ORDER BY允许您按字母或数字以及升序或降序组织结果集。

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

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。

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

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器：

ssh sammy@your_server_ip
1

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称：

mysql -u sammy -p
1

创建一个名为movieDB的数据库：

CREATE DATABASE movieDB;
1

如果数据库成功创建,您将收到这样的输出：

OutputQuery OK, 1 row affected (0.01 sec)
1

要选择movieDB数据库，运行以下USE语句：

USE movieDB;
1

OutputDatabase changed
1

选择数据库后，在其中创建一个表。在本教程的示例中，我们将创建一个表，用于存储本地电影院的放映信息。这个表将以下7个字段：

• theater_id：存储每个剧院的展厅的int数据类型的值，并作为表的主键，这意味着这一列中的每个值都将作为其各自行的唯一标识符。

• date：使用DATE数据类型来存储电影放映的年、月、日的特定日期。该数据类型遵循以下参数：4位数字表示年份，最多2位数字表示月和日(YYYY-MM-DD)。

• time：用TIME数据类型以小时、分钟和秒(HH:MM:SS)表示电影的计划放映。

• movie_name：使用varchar数据类型存储电影的名称，最多40个字符。

• movie_genre：使用不超过30个字符的varchar数据类型，保存每部电影各自类型的信息。

• guest_total：显示参加电影放映的总人数，数据类型为int。

• ticket_cost：使用decimal数据类型，精度为4，刻度为1，这意味着这一列的值可以有4位数字，小数点右侧有2位数字。这一列表示特定电影放映的票价。

通过运行下面的CREATE TABLE命令，创建一个名为movie_theater的表，其中包含这些列：

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 
1
2
3
4
5
6
7
8
9
10

接下来，向空表中插入一些示例数据：

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

OutputQuery OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0
1
2

插入数据后，就可以开始在SQL中对查询结果进行排序了。

如何使用GROUP BY

GROUP BY语句的功能是将具有共享值的记录分组。在查询中，GROUP BY语句总是与聚合函数一起使用。您可能还记得,一个聚合函数总结信息,并返回一个结果。例如，你可以查询一列的总数或总和，这将在结果中产生一个值。使用GROUP BY子句，你可以实现聚合函数，为每个你想要的组获得一个结果值。

GROUP BY对于返回多个按指定组排序的期望结果很有用，而不是只按一列排序。此外，如果你选择使用FROM语句和WHERE子句，GROUP BY必须始终位于它们之后。下面是一个使用GROUP BY和聚合函数的查询结构的示例：

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;
1

为了说明如何使用GROUP BY语句，假设你正在领导几部电影的发行活动，并且你想评估营销工作的成功。你让当地的一家剧院分享他们在周五和周六从客人那里收集的数据。首先通过运行SELECT和*符号来查看数据，以选择movie_theater表中的所有列：

SELECT * FROM movie_theater;
1

Output+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

虽然这些数据很有用，但您希望对某些特定列进行更深入的评估并排序结果。

因为你工作在几个不同类型的电影,你有兴趣知道他们被电影观众好评。具体来说，你想知道观看每种类型电影的平均人数。使用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;
1
2
3

Output+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

这个输出提供了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;
1
2
3
4

Output+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7

因为你使用了GROUP BY对date列进行分组，所以输出的结果是每天的总票房收入，在本例中是5月27日星期五7,272元，5月28日星期六9,646元。

现在假设你想关注并分析一部电影:《坏家伙》。在这种情况下，你想弄清楚时间和价格点如何影响一个家庭观看动画电影的选择。对于这个查询，使用聚合函数MAX来获取最大的ticket_cost，确保包含AS来为price_data列创建别名。然后，使用WHERE子句将结果通过movie_name缩小到只有“the Bad Guys”，并使用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;
1
2
3
4
5

Output+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7

根据这个输出，更多的客人在上午9点的早场时间去看《The Bad Guys》，这是一个更实惠的价格点，每张票8美元。然而，这些结果也显示，看电影的客人在下午5点时支付了更高的票价，为13美元，这表明家庭更喜欢在当天不会太晚的时间看电影，并且会为电影票支付更多的钱。与晚上10点的《The Bad Guys》相比，这似乎是一个公平的评价，当时只有83名观众，每张票的价格是18美元。这可以为电影院经理提供有用的信息，证明开放更多的日场和傍晚场次可以增加家庭的上座率，这些家庭正在根据首选的时间和价格点进行选择。

请注意，尽管GROUP BY几乎总是与聚合函数一起使用，但也可能有例外，尽管不太可能。然而，如果你想在没有聚合函数的情况下对结果进行分组，你可以使用DISTINCT语句来获得相同的结果。DISTINCT子句通过返回列中的唯一值来删除结果集中的重复项，并且它只能与SELECT语句一起使用。例如，如果你想将所有电影按名字分组，可以使用以下查询：

SELECT DISTINCT movie_name FROM movie_theater;
1

Output+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

在查看表中的所有数据时，你会发现电影名称有重复，因为有多个放映场次。因此，DISTINCT删除了这些重复项，并有效地将唯一的值分组在单列movie_name下。这实际上与下面的查询相同，其中包含一个GROUP BY语句：

SELECT movie_name FROM movie_theater GROUP BY movie_name;
1

现在你已经练习了在聚合函数中使用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的通用语法如下：

SELECT column_1, column_2 FROM table ORDER BY column_1;
1

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

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;
1
2

Output+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

因为你的查询指定了一个数值列，所以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;
1
2
3

Output+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9

这个结果集列出了晚上10点放映的四部不同的电影，按字母顺序降序排列，从《Top Gun Maverick》到《Downtown Abbey A New Era》。

对于下一个查询，将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;
1
2
3
4
5

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)
1
2
3
4
5
6
7
8
9

这个结果集告诉我们每部电影加上额外的12位观众的总票房收入，并将总票房收入从低到高排序。由此我们得知，《Top Gun Maverick》的票房收入最高，而《Men》的票房收入最低。与此同时，《The Bad Guys》和《Downton Abbey A New Era》在总票房上非常接近。

在本节中，你实践了多种实现ORDER BY语句的方法，以及如何指定你喜欢的顺序，例如字符型和数值型数据的升序和降序。你还学习了如何包含WHERE子句以缩小结果范围，并使用聚合函数和数学方程使用GROUP BY和ORDER BY语句执行查询。

总结

理解如何使用GROUP BY和ORDER BY语句对于对结果和数据进行排序是很重要的。你是想在一个组下组织多个结果，还是按字母和降序组织其中一列，还是同时进行这两种操作。你还了解了使用WHERE子句进一步排序结果的其他方法。