当存在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
`)