在BigQuery中模拟VALUES子句，该UNNEST写法是否为最简方案？

你问到的第一种写法，确实是BigQuery里最接近原生VALUES子句风格、同时也最简洁的实现方式：

WITH Roster AS
 (SELECT * FROM UNNEST([
    STRUCT('Adams' as LastName, 50 as SchoolID),
   ('Buchanan', 52),
   ('Coolidge', 52),
   ('Davis', 51),
   ('Eisenhower', 77)
])) SELECT * FROM Roster

对比你之前使用、也是谷歌文档里常见的UNION ALL写法：

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

这种写法需要重复编写SELECT和UNION ALL，代码冗余度更高。而你熟悉的PostgreSQL原生VALUES写法：

WITH Roster(LastName, SchoolID) AS (
    VALUES('Adams', 50),
   ('Buchanan', 52),
   ('Coolidge', 52),
   ('Davis', 51),
   ('Eisenhower', 77)
) SELECT * FROM Roster

和BigQuery的UNNEST+STRUCT写法逻辑高度一致，都是通过批量定义行数据来构建临时表，结构紧凑易读，确实是目前BigQuery里最贴近原生VALUES体验的简洁写法。

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