如何在SQL Macro中集成NVL()将NULL余额转为0并调整排序

1. 测试环境搭建 #

先创建测试用的表结构并插入示例数据：

-- 创建客户表
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100)
);

-- 创建客户账户表
CREATE TABLE customer_accounts (
    account_id NUMBER PRIMARY KEY,
    customer_id NUMBER REFERENCES customers(customer_id),
    account_type VARCHAR2(20)
);

-- 创建交易表
CREATE TABLE transactions (
    transaction_id NUMBER PRIMARY KEY,
    account_id NUMBER REFERENCES customer_accounts(account_id),
    transaction_type VARCHAR2(10) CHECK (transaction_type IN ('DEBIT', 'CREDIT')),
    amount NUMBER(12,2)
);

-- 插入测试数据
INSERT INTO customers VALUES (1, 'John Doe');
INSERT INTO customers VALUES (2, 'Melanie Zanzona'); -- 无交易的目标客户

INSERT INTO customer_accounts VALUES (101, 1, 'CHECKING');
INSERT INTO customer_accounts VALUES (102, 1, 'SAVINGS');

INSERT INTO transactions VALUES (1001, 101, 'CREDIT', 500.00);
INSERT INTO transactions VALUES (1002, 101, 'DEBIT', 150.00);
INSERT INTO transactions VALUES (1003, 102, 'CREDIT', 1000.00);

2. 修改后的SQL Macro #

将NVL()直接嵌套在余额计算的SUM()外层，同时通过排序逻辑把无交易的客户（原余额为NULL）排在末尾：

CREATE OR REPLACE FUNCTION get_customer_balance RETURN VARCHAR2 SQL_MACRO IS
BEGIN
    RETURN q'[
        SELECT 
            c.customer_id,
            c.customer_name,
            -- 用NVL将NULL余额转为0
            NVL(SUM(CASE 
                    WHEN t.transaction_type = 'CREDIT' THEN t.amount 
                    ELSE -t.amount 
                  END), 0) AS total_balance
        FROM customers c
        LEFT JOIN customer_accounts ca 
            ON c.customer_id = ca.customer_id
        LEFT JOIN transactions t 
            ON ca.account_id = t.account_id
        GROUP BY c.customer_id, c.customer_name
        ORDER BY 
            -- 优先排序：无交易的客户（SUM结果为NULL）标记为1，排在后面
            CASE WHEN SUM(CASE 
                          WHEN t.transaction_type = 'CREDIT' THEN t.amount 
                          ELSE -t.amount 
                        END) IS NULL THEN 1 ELSE 0 END,
            -- 再按余额降序排列有交易的客户
            total_balance DESC
    ]';
END;
/

3. 验证结果 #

执行查询：

SELECT * FROM get_customer_balance();

返回结果：

CUSTOMER_ID | CUSTOMER_NAME     | TOTAL_BALANCE
------------|-------------------|--------------
1           | John Doe          | 1350.00
2           | Melanie Zanzona   | 0.00

关键说明 #

• NVL(SUM(...), 0)：直接将无交易客户的NULL余额转换为0，解决显示问题。
• 排序逻辑：通过CASE判断原SUM结果是否为NULL（即无交易），给这类客户分配更高的排序值（1），确保他们在所有有交易的客户之后；有交易的客户则按余额降序排列。

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