如何在没有WITH的情况下使用子查询

Question

SELECT  Agegroup, Attrition, Attrition / (SELECT Count(Attrition) FROM project1.employee) * 100 as Attrition_rate
from (SELECT Agegroup, Count(*) as Attrition
FROM(Select
CASE
WHEN age BETWEEN 0 AND 20 THEN '<20'
WHEN age BETWEEN 20 AND 29 THEN '20-29'
WHEN age BETWEEN 30 AND 39 THEN '30-40'
WHEN age BETWEEN 40 AND 49 THEN '40-50'
WHEN age BETWEEN 50 AND 100 THEN 'Greater than 50'
ELSE 'not specified'
END AS Agegroup
FROM project1.employee
Where Attrition = TRUE) AS T
GROUP BY Agegroup) AS TT
ORDER BY Agegroup;

这将返回零个结果，但当我在BigQuery中使用WITH子句时，它可以正常工作。WITH子句在mySQL工作台中不起作用

Answer 1

mysql和BigQuery都允许with子句。也许你的数据在这些系统中不同？请提供mySQL版本的详细信息。

请考虑在over中使用windows函数，而不是嵌套查询。在BiqQuery中测试：

With employee as 
(Select 19 age, true Attrition 
union all select 20,true
union all select 50,true)
SELECT  Agegroup, Attrition, Attrition / sum(Attrition) over ()
from (SELECT Agegroup, Count(*) as Attrition
FROM(Select
CASE
WHEN age BETWEEN 0 AND 20 THEN '<20'
WHEN age BETWEEN 20 AND 29 THEN '20-29'
WHEN age BETWEEN 30 AND 39 THEN '30-40'
WHEN age BETWEEN 40 AND 49 THEN '40-50'
WHEN age BETWEEN 50 AND 100 THEN 'Greater than 50'
ELSE 'not specified'
END AS Agegroup
FROM  employee
Where Attrition = TRUE) AS T
GROUP BY Agegroup) AS TT
ORDER BY Agegroup;