如何不使用WITH子句使用子查询？MySQL执行无结果问题

一、先解决「返回零结果」的问题 #

你的原查询返回空结果，大概率是两个核心原因：

1. 布尔条件不兼容MySQL：MySQL里布尔类型实际存储为TINYINT(1)，TRUE对应数值1，FALSE对应0。原查询里的Where Attrition = TRUE在MySQL中无法匹配数据，需改成对应数值或字符串（如果Attrition是字符串类型的话）。
2. 年龄分组区间重叠：BETWEEN 0 AND 20会包含20岁，下一个条件BETWEEN 20 AND 29也包含20岁，导致分组逻辑冲突，部分数据可能被错误归类或过滤。

二、兼容MySQL（无需WITH子句）的修正写法 #

以下是适配所有MySQL版本的SQL，同时修复上述问题：

SELECT
  Agegroup,
  Attrition_count,
  ROUND(Attrition_count / total_employees * 100, 2) AS Attrition_rate
FROM (
  SELECT
    Agegroup,
    COUNT(*) AS Attrition_count
  FROM (
    SELECT
      CASE
        WHEN age < 20 THEN '<20'
        WHEN age BETWEEN 20 AND 29 THEN '20-29'
        WHEN age BETWEEN 30 AND 39 THEN '30-39' -- 修正原区间表述不一致问题
        WHEN age BETWEEN 40 AND 49 THEN '40-49' -- 统一区间格式，避免逻辑混淆
        WHEN age >= 50 THEN 'Greater than 50'
        ELSE 'not specified'
      END AS Agegroup
    FROM project1.employee
    WHERE Attrition = 1 -- 按实际字段类型调整：字符串类型用'TRUE'，数值类型用1
  ) AS age_groups
  GROUP BY Agegroup
) AS attrition_by_age
CROSS JOIN (
  SELECT COUNT(*) AS total_employees FROM project1.employee
) AS total
ORDER BY Agegroup;

三、关键修正说明 #

• 布尔条件调整：根据Attrition字段的实际类型修改WHERE条件，确保能匹配到离职员工数据。
• 年龄分组优化：将第一个条件改为age < 20，避免和后续区间重叠；同时统一区间表述，减少逻辑歧义。
• 总人数计算优化：用CROSS JOIN单独计算总员工数，比原查询的子查询更清晰，且COUNT(*)能准确统计所有员工（原写法的COUNT(Attrition)会忽略NULL值）。
• 低版本兼容：全程使用嵌套子查询替代WITH子句，适配所有不支持CTE的MySQL版本。

四、如果你的MySQL版本≥8.0（支持WITH） #

若你的MySQL Workbench连接的是8.0及以上版本，也可以用WITH写法简化逻辑，和BigQuery保持一致：

WITH age_groups AS (
  SELECT
    CASE
      WHEN age < 20 THEN '<20'
      WHEN age BETWEEN 20 AND 29 THEN '20-29'
      WHEN age BETWEEN 30 AND 39 THEN '30-39'
      WHEN age BETWEEN 40 AND 49 THEN '40-49'
      WHEN age >= 50 THEN 'Greater than 50'
      ELSE 'not specified'
    END AS Agegroup
  FROM project1.employee
  WHERE Attrition = 1
),
total_employees AS (
  SELECT COUNT(*) AS total FROM project1.employee
)
SELECT
  ag.Agegroup,
  COUNT(*) AS Attrition_count,
  ROUND(COUNT(*) / te.total * 100, 2) AS Attrition_rate
FROM age_groups ag
CROSS JOIN total_employees te
GROUP BY ag.Agegroup, te.total
ORDER BY ag.Agegroup;

内容的提问来源于stack exchange，提问作者Tanveer Ahmed