是否有用于扩展表的 SQL 函数? [英] Is there a SQL function to expand table?

问题描述

我依稀记得有一个函数可以做到这一点，但我想我可能要疯了.

假设我有一个数据表，称之为 table1.它有三列:column1、column2、column3.查询

Say I have a datatable, call it table1. It has three columns: column1, column2, column3. The query

 SELECT * FROM table1 

返回 table1 中的所有行/列.是不是有某种类型的 EXPAND 函数可以让我复制该结果?例如，如果我想将 SELECT * FROM table1 查询中的所有内容复制三次，我可以执行类似 EXPAND(3) 之类的操作?

returns all rows/columns from table1. Isn't there some type of EXPAND function that allows me to duplicate that result? For example, if I want to duplicate everything from the SELECT * FROM table1 query three times, I can do something like EXPAND(3) ?

推荐答案

在 BigQuery 中，我建议使用 CROSS JOIN:

In BigQuery, I would recommend a CROSS JOIN:

SELECT t1.*
FROM table1 CROSS JOIN
     (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3) n;

这对于大量副本来说可能会变得很麻烦，但您可以通过生成数字来简化它:

This can get cumbersome for lots of copies, but you can simplify this by generating the numbers:

SELECT t1.*
FROM table1 CROSS JOIN
     UNNEST(GENERATE_ARRAY(1, 3)) n

这将创建一个包含三个元素的数组并将其取消嵌套成行.

This creates an array with three elements and unnests it into rows.

在这两种情况下，您都可以在 SELECT 中包含 n 以区分副本.

In both these cases, you can include n in the SELECT to distinguish the copies.

这篇关于是否有用于扩展表的 SQL 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！