Oracle SQL复杂行生成需求：基于MEASURES表转换输出

现有MEASURES表结构及初始数据如下：

需要编写Oracle SQL语句将上述2行数据转换为4行输出，格式如下：

表创建与测试数据插入语句：

CREATE TABLE MEASURES("M_ID" NUMBER(10,0), "MBK_NAME" VARCHAR2(100 BYTE),"MCP_NAME" VARCHAR2(100 BYTE));
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (10,null,'MCCP');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (11,'MCCP','LOOPS');
commit;

业务逻辑规则 #

• 筛选出长度≥4字符的去重MBK_NAME/MCP_NAME（忽略null值）
• 为每个符合条件的名称关联MEASURES表中按M_ID排序的最新2条记录
• 若关联的记录数≥2，取前2条的M_ID，分别生成XXX和XXX01格式的名称；若仅1条记录，复用该M_ID生成XXX和XXX01两条记录
• 生成名称时，取原名称的前4个字符作为基础（如LOOPS取LOOP，HTTPKHN_GT取HTTP）

测试场景1 #

输入数据 #

输出结果 #

测试数据脚本 #

-- Test 1
TRUNCATE TABLE MEASURES;
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (51,'JDKJPP','JDKJPP');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (57,'JDKJPP','JDKJPP');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (61,'JDKJPP','JDKJPP');
commit;

测试场景2 #

输入数据 #

输出结果 #

测试数据脚本 #

-- Test 2
TRUNCATE TABLE MEASURES;  
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (101,'HTTPKHN_GT','HTTPKHN_GT');  
commit;

测试场景3 #

输入数据 #

输出结果 #

测试数据脚本 #

-- Test 3
TRUNCATE TABLE MEASURES;
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (15,'PIPSTT','KOOLXX');
Insert into MEASURES (M_ID,MBK_NAME,MCP_NAME) values (25,'PIPSTT','KOOLXX');
commit;

以下SQL语句完全满足上述所有需求：

WITH 
-- 收集所有符合条件的去重名称（长度≥4，非空）
valid_names AS (
    SELECT DISTINCT name AS original_name
    FROM (
        SELECT MBK_NAME AS name FROM MEASURES WHERE MBK_NAME IS NOT NULL AND LENGTH(MBK_NAME) >=4
        UNION ALL
        SELECT MCP_NAME AS name FROM MEASURES WHERE MCP_NAME IS NOT NULL AND LENGTH(MCP_NAME) >=4
    )
),
-- 为每个名称获取按M_ID排序的前2条关联记录，同时标记序号和总记录数
name_mappings AS (
    SELECT 
        vn.original_name,
        m.M_ID,
        ROW_NUMBER() OVER (PARTITION BY vn.original_name ORDER BY m.M_ID) AS rn,
        COUNT(*) OVER (PARTITION BY vn.original_name) AS total_records
    FROM valid_names vn
    LEFT JOIN MEASURES m 
        ON vn.original_name IN (m.MBK_NAME, m.MCP_NAME)
),
-- 处理单记录场景，复制生成第二条记录
expanded_mappings AS (
    SELECT original_name, M_ID, rn FROM name_mappings
    UNION ALL
    SELECT 
        original_name, 
        M_ID, 
        2 AS rn
    FROM name_mappings 
    WHERE total_records = 1 AND rn =1
),
-- 生成最终输出格式
final_output AS (
    SELECT 
        em.M_ID,
        CASE 
            WHEN em.rn =1 THEN SUBSTR(em.original_name,1,4)
            ELSE SUBSTR(em.original_name,1,4) || '01'
        END AS generated_name,
        (SELECT MBK_NAME FROM MEASURES WHERE M_ID = em.M_ID) AS MBK_NAME,
        (SELECT MCP_NAME FROM MEASURES WHERE M_ID = em.M_ID) AS MCP_NAME
    FROM expanded_mappings em
    WHERE em.rn <=2
)
SELECT * FROM final_output ORDER BY generated_name, M_ID;

关键逻辑说明 #

1. valid_names：通过UNION ALL合并两类名称，筛选出符合长度要求的去重非空值。
2. name_mappings：关联有效名称与原表数据，用窗口函数标记每条关联记录的序号，同时统计每个名称的关联总记录数。
3. expanded_mappings：针对仅关联1条记录的名称，复制该记录并标记为第2条，确保能生成XXX01格式的行。
4. final_output：根据序号生成对应格式的名称，关联原表的原始名称字段，最终按生成名称和M_ID排序输出。

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