1、场景描述
需要查询salary在4000以下、4000 ~ 6000、6000 ~ 10000以及10000以上的数量。
2、MySQL语句
SELECT salary_range,COUNT(*) AS total
FROM (SELECT CASE
WHEN salary>=10000 THEN '10000以上'
WHEN salary>=6000 AND salary<10000 THEN '6000~10000'
WHEN salary>=4000 AND salary<6000 THEN '4000~6000'
WHEN salary<4000 THEN '4000以下'
END AS salary_range
FROM tb_student_employment
WHERE 1) AS salary_summaries
GROUP BY salary_range
ORDER BY salary_range
运行结果: