Oracle SQL实现金额阶梯乘法计算并按指定格式输出

问题背景 #

现有Oracle数据库表结构：

CREATE TABLE TEST
(
    TITLE  VARCHAR2(199 BYTE),
    AMOUNT NUMBER,
    VALUE  NUMBER
)

插入数据语句：

INSERT INTO TEST (TITLE, AMOUNT, VALUE) 
VALUES ('Switch', 3000, 12); 
COMMIT;

要求将AMOUNT字段值分别乘以1到VALUE字段值，得到对应乘积结果并计算总和，输出格式如下：

Title    Amount                                                     Total

Switch   3000  3000 6000 9000  12000 15000 18000 21000 24000 27000 30000 33000 36000   231000
plug
board

实现SQL语句 #

WITH numbered_rows AS (
    SELECT 
        TITLE,
        AMOUNT,
        VALUE,
        LEVEL AS multiplier
    FROM TEST
    CONNECT BY LEVEL <= VALUE
        AND PRIOR TITLE = TITLE
        AND PRIOR SYS_GUID() IS NOT NULL
),
aggregated_data AS (
    SELECT 
        TITLE,
        AMOUNT,
        LISTAGG(AMOUNT * multiplier, ' ') WITHIN GROUP (ORDER BY multiplier) AS product_list,
        AMOUNT * VALUE * (VALUE + 1) / 2 AS total_sum
    FROM numbered_rows
    GROUP BY TITLE, AMOUNT, VALUE
)
SELECT 
    RPAD(TITLE, 8) || 
    CASE 
        WHEN AMOUNT IS NOT NULL AND VALUE IS NOT NULL THEN 
            RPAD(AMOUNT || ' ' || product_list, 60) || RPAD(total_sum, 10)
        ELSE ''
    END AS output_line
FROM TEST
LEFT JOIN aggregated_data USING (TITLE)
ORDER BY 
    CASE WHEN TITLE = 'Switch' THEN 1 ELSE 2 END,
    TITLE;

逻辑说明 #

• numbered_rows CTE：利用CONNECT BY为每个有效TITLE生成1到VALUE的乘数序列，PRIOR SYS_GUID() IS NOT NULL用于避免同一TITLE下的循环连接问题。
• aggregated_data CTE：通过LISTAGG拼接所有乘积为空格分隔的字符串；总和计算采用等差数列求和公式（1+2+...+n = n*(n+1)/2），比逐行累加更高效。
• 主查询：通过LEFT JOIN保留所有TITLE行，使用RPAD控制字段宽度实现格式对齐，同时兼容无AMOUNT或VALUE的行输出。

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