Mysql通过group by语法统计用户表里每个电话号码段的人数

由于场景中有碰到过,所以作为个人记录一下
首先用户表如下

统计出这些用户中 使用各个号码段的人数
SQL语句如下
– 统计每个号码段的使用人数

SELECT COUNT(t1.employee_id) as totaluser , t1.numlag
FROM
(
SELECT e.employee_id,e.first_name ,e.phone_number,
CASE
WHEN e.phone_number LIKE ‘130%’ THEN ‘130’
WHEN e.phone_number LIKE ‘131%’ THEN ‘131’
WHEN e.phone_number LIKE ‘132%’ THEN ‘132’
WHEN e.phone_number LIKE ‘133%’ THEN ‘133’
WHEN e.phone_number LIKE ‘134%’ THEN ‘134’
WHEN e.phone_number LIKE ‘135%’ THEN ‘135’
WHEN e.phone_number LIKE ‘136%’ THEN ‘136’
WHEN e.phone_number LIKE ‘137%’ THEN ‘137’
WHEN e.phone_number LIKE ‘138%’ THEN ‘138’
WHEN e.phone_number LIKE ‘139%’ THEN ‘139’
WHEN e.phone_number LIKE ‘157%’ THEN ‘157’
WHEN e.phone_number LIKE ‘158%’ THEN ‘158’
WHEN e.phone_number LIKE ‘159%’ THEN ‘159’
WHEN e.phone_number LIKE ‘180%’ THEN ‘180’
WHEN e.phone_number LIKE ‘182%’ THEN ‘182’
WHEN e.phone_number LIKE ‘183%’ THEN ‘183’
WHEN e.phone_number LIKE ‘185%’ THEN ‘185’
WHEN e.phone_number LIKE ‘187%’ THEN ‘187’
WHEN e.phone_number LIKE ‘189%’ THEN ‘189’
ELSE ‘0’
END AS numlag
FROM employees e

)t1

GROUP BY t1.numlag