Coding Interview Prep · 课时

数据库厂商的 PIVOT 与交叉表语法

Q: 「数据库厂商的 PIVOT 与交叉表语法」这节课中我会学到什么？

SQL Server 的 PIVOT、Postgres 的交叉表及其限制。 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

SQL Server 的 PIVOT、Postgres 的交叉表及其限制。

数据库厂商的 PIVOT 与交叉表语法 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

超越条件聚合

您已经了解了可移植的 CASE 透视写法。但面试官还希望知道，在可用时您是否能够使用特定数据库厂商的透视运算符。

SQL Server 提供专用的 PIVOT 运算符。PostgreSQL 通过 tablefunc 扩展提供 crosstab 函数。了解这两者及其容易踩坑的地方，能够体现您具备真实项目经验。

SQL Server PIVOT 结构

SQL Server 的 PIVOT 接受三项内容：

对值列执行的聚合函数。
用于指定其值将成为新列的列名的 FOR 子句。
用于指定要转换为列的字面值的 IN 列表。

它必须应用于一个派生表，而该派生表只能准确暴露键、展开列和值，不能包含其他内容。

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

隐式 GROUP BY

面试官会测试的一个隐蔽 PIVOT 易错点是：分组是隐式的。SQL Server 会按照源数据中除聚合列和 FOR 列之外的每一列进行分组。

因此，如果您的派生表意外包含了类似 order_id 的额外列，透视也会按该列分组，结果行数就会远超预期。请始终将内部查询精简为键、展开列和值。

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

带方括号的列名

在 SQL Server 中，透视后的列名是数据中的字面值，并用方括号包裹。如果某个值以数字开头或包含空格，方括号就是必需的。

您需要在外层 SELECT 中使用同样的方括号名称来选择这些列。这也是 PIVOT 无法在没有动态 SQL 的情况下处理未知值的原因：IN 列表是硬编码的。

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

PostgreSQL 交叉表

PostgreSQL 没有 PIVOT 关键字。相反，tablefunc 扩展提供了 crosstab，这是一个接受 SQL 字符串并重塑其输出的函数。

您必须先启用该扩展。crosstab 要求源查询按顺序准确返回三列：行标识符、类别和值。

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

列定义列表

crosstab 中最容易出错的部分是末尾的 AS ct(...) 列定义列表。您必须自行声明输出列的名称和类型，并且它们必须与类别的数量和顺序相匹配。

如果某一行缺少某个类别，crosstab 会按位置填充，这可能导致数据错位，除非您使用下面介绍的双参数形式。

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

双参数交叉表

当某些行缺少某些类别时，请使用双参数形式以避免错位。第二个查询会返回完整且有序的类别值列表，因此 crosstab 能够准确知道每个值所属的列。

当类别比较稀疏时，这是面试官期望您使用的稳健形式。

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQL 两者都没有

如果面试官询问 MySQL，答案很直接：MySQL 既没有 PIVOT，也没有 crosstab。在那里，您唯一的选择是使用带 CASE 的条件聚合（或使用 SUM(... ) + IF() 简写）。

这正是可移植的 CASE 模式如此受重视的原因：它是能够在所有地方运行的最小公约数。

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

实战示例：SQL Server 中的状态计数

一个报表需求是：“每个地区一行，并为每种状态设置一列来统计订单数。”在 SQL Server 中，请将精简的派生表输入 PIVOT，并使用 COUNT。

由于您统计的是状态列本身，因此分组中每个非 NULL 状态行都会被计入。外层 SELECT 会将每种状态列为一个带方括号的列。这是编写三个 COUNT(CASE ...) 表达式的简洁替代方式。

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

共同限制

PIVOT 和 crosstab 都与条件聚合有一个共同的核心限制：编写查询时必须知道输出列。

SQL Server：IN 列表是字面值列表。
PostgreSQL crosstab：列定义列表是字面值列表。

两者都无法在运行时发现类别。这需要动态构建 SQL 字符串。

应该使用哪一种

一个好的面试回答应当诚实地比较它们：

CASE 聚合：可移植、易读，并且适用于每种数据库引擎。默认选择。
SQL Server PIVOT：适合列数较多的情况，写法简洁，但隐式分组容易让人困惑。
PostgreSQL 交叉表：功能强大但较为冗长，需要扩展和列定义列表。

如果无法确定，请优先使用条件聚合，并提及厂商运算符作为替代方案。

快速检查

明确面试官会追问的 SQL Server PIVOT 行为。

回顾

一屏掌握厂商透视语法：

SQL Server：PIVOT (SUM(x) FOR col IN ([a],[b]))，并会对剩余列执行隐式 GROUP BY。
PostgreSQL：来自 tablefunc 的 crosstab()，需要列定义列表；对于稀疏数据，请使用双参数形式。
MySQL：两者都不存在，请使用 CASE。
三者都要求在编写查询时已知列。

常见问题解答

「数据库厂商的 PIVOT 与交叉表语法」课时是免费的吗？

是的 — 「数据库厂商的 PIVOT 与交叉表语法」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。

「数据库厂商的 PIVOT 与交叉表语法」这节课中我会学到什么？

SQL Server 的 PIVOT、Postgres 的交叉表及其限制。你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Coding Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「数据库厂商的 PIVOT 与交叉表语法」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Coding Interview Prep 课中编写并运行代码吗？

能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Coding Interview Prep