当存在WITH语句时，WHERE IN(SELECT)语句中断查询

Question

这非常有效：

SELECT qaer_name, 
AVG(Minutes(QA_Time)) AS avg_minutes, 
COUNT(Zendesk_URL) AS num_tickets
FROM zendeskData
WHERE qaer_name IN (SELECT Name FROM qaers)
AND tags LIKE '%cosmetic%'                    
AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
GROUP BY qaer_name

但这打破了错误：TypeError: Cannot read property '0' of undefined

WITH cosmetic AS (
SELECT qaer_name, 
AVG(Minutes(QA_Time)) AS avg_minutes, 
COUNT(Zendesk_URL) AS num_tickets
FROM zendeskData
WHERE qaer_name IN (SELECT Name FROM qaers)
AND tags LIKE '%cosmetic%'                    
AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
GROUP BY qaer_name
)
SELECT * FROM cosmetic

这个没有给出错误(但没有进行我想要的过滤(：

WITH cosmetic AS (
SELECT qaer_name, 
AVG(Minutes(QA_Time)) AS avg_minutes, 
COUNT(Zendesk_URL) AS num_tickets
FROM zendeskData
--WHERE qaer_name IN (SELECT Name FROM qaers)
WHERE tags LIKE '%cosmetic%'                    
AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
GROUP BY qaer_name
)
SELECT * FROM cosmetic

但是，这个确实给出了相同的错误：TypeError: Cannot read property '0' of undefined

WITH cosmetic AS (
SELECT qaer_name, 
AVG(Minutes(QA_Time)) AS avg_minutes, 
COUNT(Zendesk_URL) AS num_tickets
FROM zendeskData
WHERE tags LIKE '%cosmetic%'                    
AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
GROUP BY qaer_name
)
SELECT * 
FROM cosmetic
WHERE qaer_name IN (SELECT Name FROM qaers)

为什么只有当存在WITH语句时，WHERE IN (SELECT)语句才会破坏它？

编辑：这也给出了相同的错误。

let data = Database.alasql(`
;WITH cosmetic AS (
SELECT qaer_name, 
AVG(Minutes(QA_Time)) AS avg_minutes, 
COUNT(Zendesk_URL) AS num_tickets
FROM zendeskData z
WHERE EXISTS (SELECT 1 FROM qaers q WHERE z.qaer_name = q.Name) 
AND tags LIKE '%cosmetic%'                    
AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
GROUP BY qaer_name
)
SELECT * 
FROM cosmetic
`);
Logger.log(data);

Answer 1

我不知道为什么我原来的方法不起作用，但我已经设计了一个变通方法。

我的另一种选择是制作qaers表，然后在未来的查询中选择该表，将该表制作为JS数组，然后将其传递到将其格式化为列表的查询中。

这给出了我想要的正确结果，因为它允许我使用名称列表和WITH语句，这在我使查询更加复杂时是必要的。

let qaers = Database.alasql(`
SELECT MATRIX Name 
FROM annotatorData 
WHERE Pod_Number = 'QA'
`);
let data = Database.alasql(`
WITH cosmetic AS (
SELECT qaer_name, 
AVG(Minutes(QA_Time)) AS avg_minutes, 
COUNT(Zendesk_URL) AS num_tickets
FROM zendeskData
WHERE qaer_name IN ('${qaers.join("','")}')
AND tags LIKE '%cosmetic%'                    
AND CAST(sub_to_qa_date AS DATE) >= CAST('${new Date('2021-11-10')}' AS DATE)
GROUP BY qaer_name
)
SELECT * 
FROM cosmetic
`)