如何按排除cid后缀的Remark字段分组统计数据库记录数？

需求说明 #

统计每个Remark对应的记录数，但Remark字段值包含动态的cid###后缀，需排除该后缀进行分组统计。

示例

输入Remark值：

Remark1-cid11
Remark1-cid22
Remark1-cid33

分组统计结果应为3。

样本数据 #

Remark                Id    CreatedOn                        ProcessName
------------------------------------------------------------------------
Remark1-cid22          1    2023-12-27 18:02:54.4102746      P1
Remark2-cid23          2    2023-12-27 18:02:54.4102746      P1
Remark3-cid24          3    2023-12-27 18:02:54.4102746      P1
Remark4-cid25          4    2023-12-27 18:02:54.4102746      P1
Remark1-cid26          5    2023-12-27 18:02:54.4102746      P1
Remark2-cid27          6    2023-12-27 18:02:54.4102746      P1
Remark3-cid28          7    2023-12-27 18:02:54.4102746      P1
Remark4-cid29          8    2023-12-27 18:02:54.4102746      P1

期望输出 #

CreatedOn  Remark   Count   Group 
-----------------------------------
2023-12-27 Remark1   2      Group1
2023-12-27 Remark2   2      Group2
2023-12-27 Remark3   2      Group3
2023-12-27 Remark4   2      Group4 

尝试的SQL查询 #

select 
    Remark, Count (Id) 
from 
    IPocumentRequests
group by 
    Remark
having 
    Remark like '%Remark1%' 
    or Remark like '%Remark2%' 
    or Remark like '%Remark3%'  
    or Remark like '%Remark4%'

实际输出 #

Remark        (No column Name)
------------------------------
Remark1-cid11       1
Remark2-cid22       1
Remark3-cid33        1
Remark4-cid44        1
Remark1-cid55        1
Remark2-cid66        1
Remark3-cid77        1
Remark4-cid88        1

解决方案 #

核心是提取Remark中-cid之前的前缀部分，以此作为分组依据。以下是主流数据库的实现写法：

1. SQL Server / Azure SQL

用LEFT+CHARINDEX定位-cid位置，截取前缀：

SELECT
    CONVERT(date, CreatedOn) AS CreatedOn,
    LEFT(Remark, CHARINDEX('-cid', Remark) - 1) AS Remark,
    COUNT(Id) AS Count,
    'Group' + CAST(ROW_NUMBER() OVER(ORDER BY LEFT(Remark, CHARINDEX('-cid', Remark) - 1)) AS VARCHAR) AS [Group]
FROM IPocumentRequests
WHERE Remark LIKE '%-cid%'
  AND LEFT(Remark, CHARINDEX('-cid', Remark) - 1) IN ('Remark1', 'Remark2', 'Remark3', 'Remark4')
GROUP BY CONVERT(date, CreatedOn), LEFT(Remark, CHARINDEX('-cid', Remark) - 1)
ORDER BY [Group]

2. MySQL / MariaDB

用SUBSTRING_INDEX直接截取-cid之前的内容：

SELECT
    DATE(CreatedOn) AS CreatedOn,
    SUBSTRING_INDEX(Remark, '-cid', 1) AS Remark,
    COUNT(Id) AS Count,
    CONCAT('Group', ROW_NUMBER() OVER(ORDER BY SUBSTRING_INDEX(Remark, '-cid', 1))) AS `Group`
FROM IPocumentRequests
WHERE Remark LIKE '%-cid%'
  AND SUBSTRING_INDEX(Remark, '-cid', 1) IN ('Remark1', 'Remark2', 'Remark3', 'Remark4')
GROUP BY DATE(CreatedOn), SUBSTRING_INDEX(Remark, '-cid', 1)
ORDER BY `Group`

3. PostgreSQL

用SPLIT_PART分割字符串获取前缀：

SELECT
    CreatedOn::date AS CreatedOn,
    SPLIT_PART(Remark, '-cid', 1) AS Remark,
    COUNT(Id) AS Count,
    'Group' || ROW_NUMBER() OVER(ORDER BY SPLIT_PART(Remark, '-cid', 1)) AS "Group"
FROM IPocumentRequests
WHERE Remark LIKE '%-cid%'
  AND SPLIT_PART(Remark, '-cid', 1) IN ('Remark1', 'Remark2', 'Remark3', 'Remark4')
GROUP BY CreatedOn::date, SPLIT_PART(Remark, '-cid', 1)
ORDER BY "Group"

关键说明 #

• 用WHERE替代原查询的HAVING，先过滤再分组，性能更优。
• 若存在不含-cid后缀的Remark，可添加CASE判断兜底：CASE WHEN CHARINDEX('-cid', Remark) > 0 THEN ... ELSE Remark END。
• ROW_NUMBER()用于生成Group1/Group2这类有序分组标识，按前缀排序后自动生成序号。

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