Couchbase中如何通过GROUP BY实现按计划编号分组统计

原始数据 #

type  | plan_name 
918 | Plan 10   
918 | PLAN 10   
918 | PLAN10    
918 | plan 10   
918 | Plan10    
918 | Plan 11   
918 | PLAN 11   
918 | PLAN11    
918 | plan 11   
918 | Plan11    
920 | Plan 10   
920 | PLAN 10   
920 | PLAN10    
920 | plan 10   
920 | Plan10    
920 | Plan 11   
920 | PLAN 11   
920 | PLAN11    
920 | plan 11   
920 | Plan11    

需求说明 #

将plan_name中编号相同的记录（如Plan10、Plan 10等不同格式视为同一组），按不同type统计对应的计划编号及记录数量，期望输出如下：

期望输出 #

type  plan_no count
918  10      5
918  11      5
920  10      5
920  11      5

实现方案 #

核心思路是先从plan_name中提取出统一的数字编号，再按type和提取出的编号分组统计数量。以下是主流数据库的实现语句：

MySQL/MariaDB #

SELECT 
  `type`,
  REGEXP_SUBSTR(plan_name, '[0-9]+') AS plan_no,
  COUNT(*) AS count
FROM your_table_name
GROUP BY `type`, REGEXP_SUBSTR(plan_name, '[0-9]+');

说明：REGEXP_SUBSTR函数直接匹配并提取plan_name中的数字子串，随后按type和提取出的plan_no分组，用COUNT(*)统计每组记录数。

PostgreSQL #

SELECT 
  "type",
  substring(plan_name FROM '[0-9]+') AS plan_no,
  COUNT(*) AS count
FROM your_table_name
GROUP BY "type", substring(plan_name FROM '[0-9]+');

说明：通过substring结合正则表达式提取数字部分，再按指定字段分组统计。

SQL Server（2017及以上版本） #

SELECT 
  [type],
  REGEXP_REPLACE(plan_name, '[^0-9]', '') AS plan_no,
  COUNT(*) AS count
FROM your_table_name
GROUP BY [type], REGEXP_REPLACE(plan_name, '[^0-9]', '');

说明：REGEXP_REPLACE替换掉所有非数字字符，得到纯数字的计划编号，再分组统计。

通用兼容方案（适配旧版本数据库） #

如果数据库不支持正则函数，且plan_name的数字固定在末尾且长度一致，可直接截取末尾数字：

SELECT 
  `type`,
  RIGHT(plan_name, 2) AS plan_no,
  COUNT(*) AS count
FROM your_table_name
GROUP BY `type`, RIGHT(plan_name, 2);

说明：此方法仅适用于数字位置、长度固定的场景，通用性不如正则方法。

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