数据库厂商的 PIVOT 与交叉表语法
SQL Server 的 PIVOT、Postgres 的交叉表及其限制。
数据库厂商的 PIVOT 与交叉表语法 是 CoddyKit 上的免费 SQL Interview Prep 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL 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 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。
「数据库厂商的 PIVOT 与交叉表语法」这节课中我会学到什么？
SQL Server 的 PIVOT、Postgres 的交叉表及其限制。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「数据库厂商的 PIVOT 与交叉表语法」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Interview Prep 课中编写并运行代码吗？
能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。
此课程中的所有课时
- 使用条件聚合进行透视
- 数据库厂商的 PIVOT 与交叉表语法
- 将列反透视为行
- 包含未知列的动态透视