sql - 有没有办法在 SQL (BigQuery) 上加入两个查询

Question

我想在 BigQuery 上加入两个查询 A 和 B。

查询 A

SELECT membership_no, first_name, surname 
FROM `some.Table1`

查询 B

SELECT membership_no, membership_class, membership_status, join_date
FROM `some.Table2`
WHERE join_date BETWEEN '2019-01-01' AND '2019-12-31'
AND membership_status = 'ACTIVE'

有没有一种简单而优雅的方式来连接两个现有的查询？一个亲戚...

SELECT *
FROM (A)
JOIN
(B)
USING (membership_no)

无需为 A 和 B 编写代码？

标签： sqlgoogle-bigquery

Answer 1

#standardSQL
WITH queryA AS (
  SELECT membership_no, first_name, surname 
  FROM `some.Table1`
), queryB AS (
  SELECT membership_no, membership_class, membership_status, join_date
  FROM `some.Table2`
  WHERE join_date BETWEEN '2019-01-01' AND '2019-12-31'
AND membership_status = 'ACTIVE'
)
SELECT *
FROM queryA
JOIN queryB
USING (membership_no)