如何在BigQuery SQL中按学校列出球员姓名并排除空值

你的核心需求是按大学分组展示对应的NFL球员，同时移除大学为空的记录，但当前的Pivot写法方向不对——Pivot是把行转成列（比如把每个大学变成单独的列），而你要的是一行对应一个大学，列里列出该大学的所有球员。

问题出在哪？ #

你原代码里的子查询同时按collegeName和displayName分组，相当于每个球员单独成一行，完全没起到聚合的作用；后续的Pivot操作只是把指定几所大学转成列，这和“按学校展示球员”的需求不符。

正确的写法（分数据库） #

首先第一步统一要做的：用WHERE collegeName IS NOT NULL过滤掉大学为空的记录，然后按大学分组，把同校的球员名字聚合起来。不同数据库的聚合函数略有不同：

MySQL

SELECT
  collegeName,
  GROUP_CONCAT(DISTINCT displayName SEPARATOR ', ') AS players
FROM NFL.Players
WHERE collegeName IS NOT NULL
GROUP BY collegeName
ORDER BY collegeName;

SQL Server

SELECT
  collegeName,
  STRING_AGG(DISTINCT displayName, ', ') WITHIN GROUP (ORDER BY displayName) AS players
FROM NFL.Players
WHERE collegeName IS NOT NULL
GROUP BY collegeName
ORDER BY collegeName;

BigQuery/PostgreSQL

如果想要数组格式的球员列表：

SELECT
  collegeName,
  ARRAY_AGG(DISTINCT displayName ORDER BY displayName) AS players
FROM NFL.Players
WHERE collegeName IS NOT NULL
GROUP BY collegeName
ORDER BY collegeName;

如果想要字符串拼接的格式（PostgreSQL可用）：

SELECT
  collegeName,
  STRING_AGG(DISTINCT displayName, ', ' ORDER BY displayName) AS players
FROM NFL.Players
WHERE collegeName IS NOT NULL
GROUP BY collegeName
ORDER BY collegeName;

如果你确实需要用Pivot（行转列） #

如果你的需求是把指定大学作为列，每列显示该大学的球员（比如一行展示多所大学的球员），那修正后的代码可以这样写（以BigQuery为例）：

SELECT *
FROM (
  SELECT collegeName, displayName
  FROM NFL.Players
  WHERE collegeName IS NOT NULL
    AND collegeName IN ('Kansas State','Abilene Christian','Texas Tech')
)
PIVOT (
  STRING_AGG(displayName, ', ') AS players
  FOR collegeName IN ('Kansas State','Abilene Christian','Texas Tech')
);

内容的提问来源于stack exchange，提问作者Ryan_Brusseau