用 json_agg 查询飞机座位图

Q: 如何在 SQL 中把值聚合为数组？

在 PostgreSQL 中，用 array_agg() 生成数组，或用 json_agg() 生成 JSON 数组，并在调用内部用 ORDER BY 对元素排序。在 MySQL 中使用 JSON_ARRAYAGG() 或 GROUP_CONCAT()；在 SQL Server 中使用 STRING_AGG()。

Q: 如何在 SQL 中把字符串拆分为数字部分和字母部分？

如果字母在末尾，rtrim(value, 'ABC…') 返回数字部分，right(value, 1) 返回最后一个字母。对于任意格式，请使用正则表达式，例如 PostgreSQL 中的 regexp_match(value, '^(\d+)([A-Z])$') 或 MySQL 中的 REGEXP_SUBSTR()。

Q: 为什么我的数字排序成了 1、10、11、2？

因为它们以文本形式存储或计算，而文本是逐字符比较的。排序前先转换为数字：PostgreSQL 中用 ORDER BY value::int，MySQL 中用 ORDER BY CAST(value AS UNSIGNED)。

第 12.3 课 · 阅读时间：约 5 分钟 · 练习题 ↓

本课讲解 Bookings 数据库（PostgreSQL）上的 SQL 题目飞机座位图的解法。你将看到如何用字符串函数把 12A 这样的座位号拆分为排号和字母，如何按排和舱位等级对座位分组，以及如何用 json_agg 把每一排的字母汇总成排好序的 JSON 数组。

题目：生成 Boeing 777-300 的座位图。结果的每一行描述客舱中的一排座位：排号（row）、该排座位字母组成的按字母顺序排列的 JSON 数组（seats），以及舱位等级（fare_conditions）。例如，商务舱第一排是 1、["A", "C", "D", "G", "H", "K"]、Business。

请先尝试自己解答；完整的查询隐藏在本课末尾。

数据在哪里

seats 存储每种机型的全部座位：aircraft_code、seat_no（如 12A）和 fare_conditions。
aircrafts_data 存储机型；名称位于 JSON 列 model 中，包含多种语言：{ "en": "Boeing 777-300", "ru": "Боинг 777-300" }。运算符 ->> 以文本形式提取值：model->>'en'。

没有单独的排号列：排号和字母都包含在 seat_no 中。

分步解答

1. 拆分座位号

SELECT seat_no, rtrim(seat_no, 'ABCDEFGHKJ') AS row_text, right(seat_no, 1) AS letter
FROM seats
WHERE aircraft_code = '773' AND seat_no IN ('1A', '11D');

结果：1A 得到排号 1 和字母 A；11D 得到排号 11 和字母 D。

rtrim(seat_no, 'ABCDEFGHKJ') 从字符串末尾删除列出的任意字符，只留下数字。这个列表包含数据库中用到的所有字母；飞机座位图不使用字母 I，因为它容易和 1 混淆。
right(seat_no, 1) 取最后一个字符，即座位字母。

更通用的做法是使用正则表达式：(regexp_match(seat_no, '^(\d+)([A-Z])$'))[1] 无论字母是什么都能返回数字部分。

2. 按排和舱位等级分组

结果中的一行对应某个舱位等级的一排座位，因此按提取出的排号和 fare_conditions 分组。按表达式分组与按列分组的方式相同；随后在 SELECT 中使用同一个表达式。

3. 把字母汇总成 JSON 数组

json_agg(value) 是聚合函数：与 COUNT 或 SUM 一样，它处理一个分组中的所有行，但把它们作为一个 JSON 数组返回。元素的顺序通过函数调用内部的 ORDER BY 指定：

json_agg(right(seat_no, 1) ORDER BY right(seat_no, 1))

如果内部没有 ORDER BY，字母会按照读取行的顺序排列，例如 ["C", "A", "D"]。

4. 按数字对排号排序

rtrim() 返回文本，而文本是逐字符排序的：1、11、12、… 19、2、20。要按数字对排号排序，请把排号转换为整数：rtrim(seat_no, 'ABCDEFGHKJ')::int。

结果：46 行。商务舱占第 1–5 排（每排 6 个座位），舒适舱（Comfort）占第 11–16 排，经济舱占第 17–51 排。没有第 6–10 排，最后的第 51 排只有 4 个座位：["D", "E", "F", "G"]。

其他数据库中的类似函数

数据库	汇总分组中的值
PostgreSQL	`json_agg(x ORDER BY x)`、`array_agg(x ORDER BY x)`、`string_agg(x, ', ' ORDER BY x)`
MySQL 8.0	`JSON_ARRAYAGG(x)`（不保证顺序）、`GROUP_CONCAT(x ORDER BY x)`
SQL Server	`STRING_AGG(x, ', ') WITHIN GROUP (ORDER BY x)`

剧透：解答的 SQL 查询隐藏在下方，点击展开。


select
    rtrim(seat_no, 'ABCDEFGHKJ')::int "row",
    json_agg(right(seat_no, 1) order by right(seat_no, 1)) seats,
    fare_conditions
from seats
join aircrafts_data using (aircraft_code)
where aircrafts_data.model->>'en' = 'Boeing 777-300'
group by rtrim(seat_no, 'ABCDEFGHKJ'), fare_conditions
order by "row";

要点：

当两个值存储在同一个字符串中时，用字符串函数把它们分开：rtrim() 取数字，right() 取最后一个字母，regexp_match() 适用于任意格式。
可以按表达式进行 GROUP BY，例如按提取出的排号分组。
json_agg() 把一个分组中的值汇总为 JSON 数组；调用内部的 ORDER BY 决定元素顺序。
从文本中提取的数字必须转换为 int 才能正确排序。
row 在 PostgreSQL 中是关键字，所以作为列名时要用双引号括起来："row"。

常见问题

如何在 SQL 中把值聚合为数组？

在 PostgreSQL 中，用 array_agg() 生成数组，或用 json_agg() 生成 JSON 数组，并在调用内部用 ORDER BY 对元素排序。在 MySQL 中使用 JSON_ARRAYAGG() 或 GROUP_CONCAT()；在 SQL Server 中使用 STRING_AGG()。

如何在 SQL 中把字符串拆分为数字部分和字母部分？

如果字母在末尾，rtrim(value, 'ABC…') 返回数字部分，right(value, 1) 返回最后一个字母。对于任意格式，请使用正则表达式，例如 PostgreSQL 中的 regexp_match(value, '^(\d+)([A-Z])$') 或 MySQL 中的 REGEXP_SUBSTR()。

为什么我的数字排序成了 1、10、11、2？

因为它们以文本形式存储或计算，而文本是逐字符比较的。排序前先转换为数字：PostgreSQL 中用 ORDER BY value::int，MySQL 中用 ORDER BY CAST(value AS UNSIGNED)。