Oracle 11g 按表名模式分类分组统计表数量的SQL实现

核心问题根因 #

你现有SQL的分组逻辑存在本质偏差：

• 你当前是按OBJECT_NAME（单个表名）做分组聚合，由于dba_objects视图中，同一schema下同一类型、同一名称的表本身就是唯一记录，所以每个分组的计数值恒为1，完全达不到“按表名匹配规则归类到对应分组后，统计分组内总表量”的目标。
• 你需要的统计维度是CASE语句生成的TABLE_GROUP，而非单个表名，调整分组维度后就能得到非1的聚合结果。

实现方案 #

完全可以使用WITH CTE实现，这种写法将标签生成和聚合统计逻辑解耦，可读性、可维护性远高于嵌套子查询，且完全适配Oracle 11g的语法支持。
方案核心逻辑：

1. 用CTE层单独完成所有表的分组标签打标，避免聚合阶段重复编写CASE逻辑
2. 直接按TABLE_GROUP维度做聚合，统计每个分组的总表数
3. 如果需要匹配你期望的输出结构（每个分组展示一个代表性表名），直接取分组内的任意一个匹配表名作为展示值即可。

可直接运行的优化SQL #

WITH table_group_tag AS (
    -- CTE层：为所有符合权限范围的表生成分组标签
    SELECT 
        OBJECT_NAME,
        CASE 
            WHEN OBJECT_NAME LIKE '%AB_ABC%' THEN 'ABC'
            WHEN OBJECT_NAME LIKE 'AB_BBC%' THEN 'BBC'
            WHEN OBJECT_NAME LIKE 'AB_CNBC%' THEN 'CNBC'
            WHEN OBJECT_NAME LIKE 'AB_PTV%' THEN 'PTV'
            WHEN OBJECT_NAME LIKE 'AB_CNN%' THEN 'CNN'
            WHEN OBJECT_NAME LIKE '%TMP%' THEN 'TEMP'
            WHEN OBJECT_NAME LIKE '%TEMP%' THEN 'TEMP'
            ELSE 'OTHER'
        END AS TABLE_GROUP
    FROM dba_objects
    WHERE 
        owner = 'SCHEMA_NAME'
        AND object_type = 'TABLE'
)
-- 聚合层：按分组维度统计总数量
SELECT
    MIN(OBJECT_NAME) AS OBJECT_NAME, -- 取分组内字典序最小的表名作为代表，匹配需求的输出结构
    TABLE_GROUP,
    COUNT(*) AS COUNTS
FROM table_group_tag
WHERE TABLE_GROUP != 'OTHER' -- 按需开启，排除未匹配到规则的其他表统计
GROUP BY TABLE_GROUP
ORDER BY TABLE_GROUP;

与原SQL的差异优化点 #

• 移除了冗余的DISTINCT关键字：分组聚合本身就会对结果去重，额外加DISTINCT只会增加数据库计算开销
• 逻辑解耦：后续如果需要调整分组匹配规则、增加筛选条件，只需要修改CTE层的代码，无需改动聚合逻辑
• 分组维度修正：从按单个表名分组调整为按分组标签分组，最终统计的就是每个分类下的总表数，和你期望的120、130这类计数值逻辑完全一致

注意事项 #

• 你原有CASE语句的判断顺序存在优先级隐患：当前两个TEMP的匹配规则顺序不影响结果，但后续新增规则时，需要把匹配范围更窄、更具体的规则放在前面，避免被范围更宽的规则提前命中导致分类错误
• 你原SQL中注释掉的一堆NOT LIKE筛选逻辑，本质是要排除“OTHER”分组的统计，完全不需要在原查询层写冗余的匹配判断，只需要在聚合层增加对应的WHERE条件即可，代码更简洁也不容易出错
• 如果不需要展示每个分组的代表表名，只需要输出分组名和对应计数值，可以直接移除SELECT中的MIN(OBJECT_NAME)字段，查询效率会进一步提升

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