SQL处理字符串型员工数：筛选指定规模的德克萨斯企业

核心思路 #

先统一清理字段格式（移除千分位逗号），再通过字符串特征区分单值/范围格式，分别提取数值后完成区间判断。

1. 关键处理步骤 #

• 用REPLACE函数去掉字段中的逗号（如把"3,000"转为"3000"），确保数值能正常转换
• 用LOCATE('to', 字段)判断是否为范围格式（返回0则为单值，否则为范围）

2. 完整筛选SQL语句 #

SELECT *
FROM your_table_name
WHERE
  -- 单值情况：数值落在25-300或301-1000区间
  (LOCATE('to', Employee_count) = 0 
   AND CAST(REPLACE(Employee_count, ',', '') AS UNSIGNED) BETWEEN 25 AND 1000)
  OR
  -- 范围情况：完全落在25-300区间内
  (LOCATE('to', Employee_count) > 0
   AND CAST(TRIM(SUBSTRING_INDEX(REPLACE(Employee_count, ',', ''), 'to', 1)) AS UNSIGNED) >= 25
   AND CAST(TRIM(SUBSTRING_INDEX(REPLACE(Employee_count, ',', ''), 'to', -1)) AS UNSIGNED) <= 300)
  OR
  -- 范围情况：完全落在301-1000区间内
  (LOCATE('to', Employee_count) > 0
   AND CAST(TRIM(SUBSTRING_INDEX(REPLACE(Employee_count, ',', ''), 'to', 1)) AS UNSIGNED) >= 301
   AND CAST(TRIM(SUBSTRING_INDEX(REPLACE(Employee_count, ',', ''), 'to', -1)) AS UNSIGNED) <= 1000);

3. 语句细节解释 #

• SUBSTRING_INDEX(..., 'to', 1)：提取范围格式中的最小值部分，TRIM用于移除前后空格
• SUBSTRING_INDEX(..., 'to', -1)：提取范围格式中的最大值部分，同样转成数值类型
• 条件拆分：分别处理单值和两种范围场景，确保符合筛选需求

4. 灵活调整（可选） #

如果需要筛选与目标区间有交集的企业（比如"20 to 400"这类跨区间的范围），可以把范围判断条件简化为：

(LOCATE('to', Employee_count) > 0
 AND CAST(TRIM(SUBSTRING_INDEX(REPLACE(Employee_count, ',', ''), 'to', -1)) AS UNSIGNED) >= 25
 AND CAST(TRIM(SUBSTRING_INDEX(REPLACE(Employee_count, ',', ''), 'to', 1)) AS UNSIGNED) <= 1000)

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