這完美地作業：
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)
為什么WHERE IN (SELECT)只有在有WITH陳述句存在時陳述句才會破壞它？
編輯：這也給出了同樣的錯誤。
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);
uj5u.com熱心網友回復：
我不知道為什么我原來的方法不起作用，但我設計了一個變通方法。
我創建一個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
`)
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/361500.html