MySQL如何自动按地址中的城市分组统计员工数据？

要实现自动识别地址中的城市并分组统计数量，核心是从present_address字段中提取城市名称，再按城市分组计数。以下是具体解决方案：

适用MySQL 8.0+的SQL语句 #

SELECT 
    CONCAT(
        INITCAP(REPLACE(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(present_address, ',', -2), ',', 1)), ' CITY', '')),
        ' - ',
        COUNT(*)
    ) AS city_count
FROM emply1
GROUP BY 
    REPLACE(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(present_address, ',', -2), ',', 1)), ' CITY', '')
ORDER BY COUNT(*) DESC;

低版本MySQL（无INITCAP函数）的替代方案 #

如果你的MySQL版本低于8.0，用以下语句实现城市名首字母大写：

SELECT 
    CONCAT(
        UPPER(SUBSTRING(city_name, 1, 1)),
        LOWER(SUBSTRING(city_name FROM 2)),
        ' - ',
        COUNT(*)
    ) AS city_count
FROM (
    SELECT 
        REPLACE(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(present_address, ',', -2), ',', 1)), ' CITY', '') AS city_name
    FROM emply1
) AS subquery
GROUP BY city_name
ORDER BY COUNT(*) DESC;

语句逻辑说明 #

1. 提取城市部分：

   • SUBSTRING_INDEX(present_address, ',', -2)：截取地址中倒数第二个逗号到结尾的内容（例如从BUNK HOUSE #4 CANLUBANG, CALAMBA CITY, LAGUNA得到 CALAMBA CITY, LAGUNA）
   • SUBSTRING_INDEX(..., ',', 1)：从上述结果中截取第一个逗号前的内容（得到 CALAMBA CITY）
   • TRIM(...)：去除城市名称前后的空格（得到CALAMBA CITY）
   • REPLACE(..., ' CITY', '')：去掉城市名后的 CITY后缀（最终得到CALAMBA）

2. 格式化与统计：

   • 用INITCAP或自定义字符串处理实现城市名首字母大写
   • CONCAT将城市名和统计数拼接成城市 - 数量的格式
   • GROUP BY按提取后的城市名分组，COUNT(*)统计每组条目数
   • ORDER BY COUNT(*) DESC按数量降序排列，匹配你期望的结果格式

注意事项 #

• 该方案依赖地址格式的一致性：城市部分必须位于倒数第二个逗号与最后一个逗号之间，且城市名后缀仅为 CITY。如果地址格式有其他变化（如出现 TOWN后缀），需要扩展REPLACE函数的处理逻辑。
• 长期来看，建议将地址拆分为street、city、province等独立字段，这样分组统计会更准确，也避免字符串解析的潜在问题。

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