BigQuery：关联订单与发票并整合关联订单费用的查询实现

示例表 #

订单表（Orders Table） #

发票表（Invoices Table） #

需求与期望输出 #

需要将两张表关联，让主订单及其关联订单的所有费用都显示在对应的主订单行中，期望输出如下：

期望输出 #

尝试过程 #

最初仅能查询出主订单对应的发票，SQL语句如下：

SELECT
  orders,
  ARRAY(
  SELECT AS STRUCT * FROM `invoices_table` WHERE order=orders.order_id) AS invoice
FROM
  `orders_table` AS orders

尝试用UNION子查询整合主订单及关联订单的发票时，触发了关联子查询错误，语句如下：

SELECT
  orders,
  ARRAY(
  SELECT AS STRUCT * FROM 
(SELECT * FROM `invoices_table` WHERE order=orders.order_id
    UNION ALL SELECT * FROM `invoices_table` WHERE linked_order_id=orders.linked_order1
    UNION ALL SELECT * FROM `invoices_table` WHERE linked_order_id=orders.linked_order2)
) AS invoice
FROM
  `orders_table` AS orders

解决方案 #

实际更简单的写法可以实现需求，通过OR和IN条件匹配主订单ID或关联订单ID，避免子查询嵌套带来的问题：

SELECT
  orders,
  ARRAY(
  SELECT AS STRUCT * FROM `invoices_table` WHERE order=orders.order_id OR linked_order_id IN(orders.linked_order1, orders.linked_order2)) AS invoice
FROM
  `orders_table` AS orders

内容的提问来源于stack exchange，提问作者mister_b