Bigquery: STRUCT (*)语法
Bigquery: STRUCT (*)语法
提问于 2020-02-17 15:03:52
如何在不指定名称的情况下自动将STRUCT应用于表中的所有字段？
不起作用的示例：
WITH data as (
SELECT 'Alex' as name, 14 as age, 'something else 1' other_field
UNION ALL
SELECT 'Bert' as name, 14 as age, 'something else 2' other_field
UNION ALL
SELECT 'Chiara' as name, 13 as age, 'something else 3' other_field
)
SELECT AS STRUCT(SELECT * except (other_field) from data) as student_data返回：Error: Scalar subquery cannot have more than one column unless using SELECT AS STRUCT to build STRUCT values at [9:17]
然而，这样做是可行的：
WITH data as (
SELECT 'Alex' as name, 14 as age, 'something else 1' other_field
UNION ALL
SELECT 'Bert' as name, 14 as age, 'something else 2' other_field
UNION ALL
SELECT 'Chiara' as name, 13 as age, 'something else 3' other_field
)
SELECT STRUCT(name,age) as student_data
from data问题是，一旦我有100列，其中只有5不属于，这使我疯狂地写出它们。是否有更简单的方法使用某些版本的Select * Except()？
回答 3
Stack Overflow用户
回答已采纳
发布于 2020-02-17 15:07:44
下面是BigQuery标准SQL
#standardSQL
WITH data AS (
SELECT 'Alex' AS name, 14 AS age, 'something else 1' other_field UNION ALL
SELECT 'Bert' AS name, 14 AS age, 'something else 2' other_field UNION ALL
SELECT 'Chiara' AS name, 13 AS age, 'something else 3' other_field
)
SELECT (
SELECT AS STRUCT * EXCEPT(other_field)
FROM UNNEST([t])
) AS student_data
FROM data t 带输出
Row student_data.name student_data.age
1 Alex 14
2 Bert 14
3 Chiara 13 Stack Overflow用户
发布于 2020-12-15 09:08:51
作为对Mikhail的答复的更新，https://stackoverflow.com/a/60265292/413531，因为评论部分不允许我使用正确的格式：
请注意，您可能需要将SELECT AS STRUCT * EXCEPT(other_field)包装在()中。不需要UNNEST(t)部分。也就是说，这也适用于：
#standardSQL
WITH data AS (
SELECT 'Alex' AS name, 14 AS age, 'something else 1' other_field UNION ALL
SELECT 'Bert' AS name, 14 AS age, 'something else 2' other_field UNION ALL
SELECT 'Chiara' AS name, 13 AS age, 'something else 3' other_field
)
SELECT
(SELECT AS STRUCT data.* EXCEPT(other_field)) as student_data,
FROM data
Stack Overflow用户
发布于 2020-02-17 15:08:18
您需要AS STRUCT expr的表达式
SELECT AS STRUCT data.* except (other_field) from data页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60265224
复制相关文章
相似问题
腾讯云开发者
