mysql 在BigQuery中生成动态FROM子句

7rfyedvj  于 2022-12-10  发布在  Mysql
关注(0)|答案(1)|浏览(117)

我有一个过程,负责从四个输入参数构建FROM子句:初始查询(初始表的SELECT子句)、辅助表、辅助表的UNION字段和初始表的UNION字段
以下是初始查询:

SELECT * FROM INIT_TABLE AS INIT

这是子表:

'TABLE_ONE'

以下是子表的字段:

'fieldOneT2,fieldTwoT2'

以下是原始表中的字段:

'fieldInit1,fieldInit2'

以下是程序:

SELECT concat(queryFrom, ' left join ', tableJoin, ' AS ', tableJoin, ' ON INIT.',
              columnRel, ' = ', tableJoin, '.', columnJoin) as fromClause

这是它返回的结果:

SELECT * 
FROM INIT_TABLE INIT 
left join TABLE_ONE AS TABLE_ONE 
  ON INIT.fieldInit1,fieldInit2 = TABLE_ONE.fieldOneT2,fieldTwoT2

这就是我想要实现的:

SELECT * 
FROM INIT_TABLE INIT 
left join TABLE_ONE AS TABLE_ONE 
  ON INIT.fieldInit1 = TABLE_ONE.fieldOneT2
  ON INIT.fieldInit2 = TABLE_ONE.fieldTwoT2
8i9zcol2

8i9zcol21#

对于BigQuery,请考虑下面的内容。

DECLARE queryFrom DEFAULT 'SELECT * \n FROM INIT_TABLE AS INIT';
DECLARE tableJoin DEFAULT 'TABLE_ONE';
DECLARE columnRel DEFAULT 'fieldInit1,fieldInit2';
DECLARE columnJoin DEFAULT 'fieldOneT2,fieldTwoT2';

SELECT concat(queryFrom, '\n left join ', tableJoin, ' AS ', tableJoin, '\n    ON ' ) ||
       (SELECT STRING_AGG('INIT.' || r || ' = ' || tableJoin || '.' || j, '\n  AND ')
          FROM UNNEST(SPLIT(columnRel)) r WITH offset
          JOIN UNNEST(SPLIT(columnJoin)) j WITH offset USING (offset))
       as fromClause;

查询结果

相关问题