在Digital Metaphors ReportBuilder中用SQL统计子串数量

我来帮你搞定这个报表统计需求！核心思路是先把locations字段里的复合条目拆分开，提取出每个条目中的building名称，再和你的building表关联统计次数。下面分不同数据库场景给出具体SQL：

1. 适用于SQL Server的方案 #

SQL Server自带的STRING_SPLIT函数可以轻松拆分逗号分隔的字符串，步骤如下：

-- 第一步：拆分locations字段为单独的条目
WITH SplitLocations AS (
    SELECT 
        id,
        value AS location_entry
    FROM 
        -- 替换成你存储id和locations的表名
        your_locations_table
    CROSS APPLY 
        STRING_SPLIT(locations, ',')
),
-- 第二步：从每个条目中提取building名称（冒号前的部分）
ExtractedBuildings AS (
    SELECT 
        LEFT(location_entry, CHARINDEX(':', location_entry) - 1) AS building_name
    FROM 
        SplitLocations
)
-- 第三步：关联building表统计次数
SELECT 
    b.building,
    COUNT(eb.building_name) AS occurrence_count
FROM 
    -- 替换成你的building表名
    your_building_table b
LEFT JOIN 
    ExtractedBuildings eb ON b.building = eb.building_name
GROUP BY 
    b.building
ORDER BY 
    occurrence_count DESC;

2. 适用于MySQL的方案 #

MySQL没有内置的字符串拆分函数，我们可以用递归CTE来实现拆分：

-- 递归拆分locations字段
WITH RECURSIVE SplitLocations AS (
    SELECT 
        id,
        locations AS remaining,
        SUBSTRING_INDEX(locations, ',', 1) AS location_entry,
        1 AS level
    FROM 
        -- 替换成你存储id和locations的表名
        your_locations_table
    UNION ALL
    SELECT 
        id,
        SUBSTRING(remaining, LENGTH(location_entry) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining, LENGTH(location_entry) + 2), ',', 1),
        level + 1
    FROM 
        SplitLocations
    WHERE 
        remaining != ''
),
-- 提取building名称
ExtractedBuildings AS (
    SELECT 
        SUBSTRING_INDEX(location_entry, ':', 1) AS building_name
    FROM 
        SplitLocations
    WHERE 
        location_entry != ''
)
-- 关联统计
SELECT 
    b.building,
    COUNT(eb.building_name) AS occurrence_count
FROM 
    -- 替换成你的building表名
    your_building_table b
LEFT JOIN 
    ExtractedBuildings eb ON b.building = eb.building_name
GROUP BY 
    b.building
ORDER BY 
    occurrence_count DESC;

关键说明 #

• 记得把代码中的your_building_table和your_locations_table替换成你实际的表名；
• 用LEFT JOIN可以保证即使某个building在locations中完全没出现，也会显示0次，不会漏掉数据；
• 如果你的ReportBuilder连接的是其他数据库（比如Oracle），可以告诉我，我再调整对应的拆分逻辑～

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