有没有一种方法可以在一个select语句中取消对数组的测试

Question

我有以下内容：

CTE AS (
SELECT 
IssueID, CompanyName, Customer.ID FROM
( SELECT
IssueID,
REGEXP_EXTRACT_ALL(CompanyName,'[a-zA-Z]{3,4}[0-9]{3,4}') AS CompanyName
FROM 
`DWH.website` Website
LEFT JOIN  `DWH.issues` AS Issue ON DWH.IssueID = Issue.ID) AS BASE
CROSS JOIN UNNEST (CompanyName) AS CompanyName
LEFT JOIN 
DWH.Customer ON  CompanyName = Customer.CompanyName
)

我想知道是否有更有效的方法来写这篇文章，特别是是否有一种方法可以在一个Select Statement中实现UNNEST。

期望输出示例

ID19435222345[/tr>

IssueID	公司名称
18677	谷歌
18677	罗技
34564	目标	76845

它看起来像是更大查询的一部分(因为缺少DWH和Customer等一些表(，但是，假设它有效，您可以尝试以下操作：

CTE AS (
SELECT IssueID, CompanyName, Customer.ID 
FROM `DWH.website` Website
LEFT JOIN `DWH.issues` AS Issue ON DWH.IssueID = Issue.ID
CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(CompanyName,'[a-zA-Z]{3,4}[0-9]{3,4}')) AS CompanyName
LEFT JOIN DWH.Customer ON CompanyName = Customer.CompanyName
)