Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
406 views
in Technique[技术] by (71.8m points)

sql - union all two table instead of join

I have several table which I can not join them as it gets really complicated and bigquery is not able to process it. So I am trying to union all tables and then group by. I have an issue during this process. I have two tables called t1 and t2 with below headers, they don't have null values:

a.   b.   c.   d.                a.   b.   c.   e.   

so in order to union all and group them I have below code:

WITH
  all_tables_unioned AS (
  SELECT
    *,
    NULL e
  FROM
    `t1`
  UNION ALL
  SELECT
    *,
    NULL d
  FROM
    `t2` )
SELECT
  a,
  b,
  c,
  MAX(d) AS d,
  MAX(e) AS e
FROM
  all_tables_unioned
GROUP BY
  a,
  b,
  c

unfortunately when I run this I get a table a,b,c,d,e which e column is all null! I tried to run query for each table before union all to make sure they are not null. I do not really know what is wrong with my query.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

union all does not go by column names. Just list all the columns explicitly:

WITH all_tables_unioned AS (
      SELECT a, b, c, d, NULL as e
      FROM `t1`
      UNION ALL
      SELECT a, b, c, NULL as d, e
      FROM `t2`
     )

Regardless of the names you assign, the union all uses positions for matching columns.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...