有没有办法在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 编写代码？

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)