Oracle SQL查询求助：客户余额统计与应收/应付虚拟列实现

我来帮你调整这个SQL查询，先梳理下原查询里的几个核心问题，再给出符合需求的修正版本：

原查询存在的问题 #

• 分组逻辑错误：GROUP BY中包含了a.CREDIT_DEBIT_AMT，这会导致按每条明细金额分组，无法得到客户维度的总借贷金额
• 判断逻辑偏差：CASE语句用单条明细的金额做判断，而不是客户的汇总金额，不符合需求
• 缺失必填字段：没有选择需求要求的c.CUST_ID字段

修正后的SQL查询 #

SELECT 
    c.CUST_ID,
    c.LOGIN_ID,
    SUM(TO_NUMBER(a.CREDIT_DEBIT_AMT)) AS "Outstanding Amt",
    CASE 
        WHEN SUM(TO_NUMBER(a.CREDIT_DEBIT_AMT)) < 0 THEN 'Due from Customer'
        ELSE 'Due to Customer'
    END AS "Due"
FROM ISBS_CUSTOMER_MST c
JOIN ISBS_ACCT_LEDGER_DET a 
    ON c.CUST_ID = a.CUST_ID
GROUP BY c.CUST_ID, c.LOGIN_ID;

关键改动说明 #

1. 数据类型转换：注意到CREDIT_DEBIT_AMT是VARCHAR2类型，必须用TO_NUMBER()转换为数值后再求和，避免出现字符串求和错误
2. 分组维度调整：GROUP BY只保留c.CUST_ID和c.LOGIN_ID，确保按单个客户汇总所有借贷记录
3. 状态判断修正：基于客户的总借贷金额判断状态：
   • 总金额为负（借方）：客户欠款，显示Due from Customer
   • 总金额非负（贷方或平）：需支付给客户，显示Due to Customer
4. 补充必填字段：添加了需求要求的CUST_ID字段

可选扩展：包含无借贷记录的客户 #

如果需要展示所有客户（包括没有借贷明细的客户，总金额显示为0），可以使用LEFT JOIN并处理NULL值：

SELECT 
    c.CUST_ID,
    c.LOGIN_ID,
    NVL(SUM(TO_NUMBER(a.CREDIT_DEBIT_AMT)), 0) AS "Outstanding Amt",
    CASE 
        WHEN NVL(SUM(TO_NUMBER(a.CREDIT_DEBIT_AMT)), 0) < 0 THEN 'Due from Customer'
        ELSE 'Due to Customer'
    END AS "Due"
FROM ISBS_CUSTOMER_MST c
LEFT JOIN ISBS_ACCT_LEDGER_DET a 
    ON c.CUST_ID = a.CUST_ID
GROUP BY c.CUST_ID, c.LOGIN_ID;

原始表结构与测试数据 #

客户表结构

CREATE TABLE isbs_customer_mst (
 cust_id VARCHAR2(30) NOT NULL,
 login_id VARCHAR2(30) NOT NULL,
 cust_nm VARCHAR2(30),
 cust_addr VARCHAR2(300),
 CONSTRAINT isbs_customer_mst_pk PRIMARY KEY (cust_id)
);

客户测试数据

INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR) VALUES ('CUST0000000001', 'USER1', 'User Login ID 1', '143/1 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');
INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR) VALUES ('CUST0000000002', 'USER2', 'User Login ID 2', '143/2 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');
INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR) VALUES ('CUST0000000003', 'USER3', 'User Login ID 3', '143/3 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');
INSERT INTO ISBS_CUSTOMER_MST (CUST_ID, LOGIN_ID, CUST_NM, CUST_ADDR) VALUES ('CUST0000000004', 'USER4', 'User Login ID 4', '143/4 Uthamar Gandhi Salai, Nungambakkam, Chennai - 34');

借贷明细表结构

CREATE TABLE isbs_acct_ledger_det (
 acct_ledger_id VARCHAR2(30),
 cust_id VARCHAR2(30),
 credit_debit_amt VARCHAR2(30) NOT NULL,
 credit_debit_dttm TIMESTAMP NOT NULL,
 CONSTRAINT isbs_acct_ledger_det_pk PRIMARY KEY (acct_ledger_id),
 CONSTRAINT isbs_acct_ledger_det_fk FOREIGN KEY (cust_id) REFERENCES isbs_customer_mst (cust_id)
);

借贷明细测试数据

INSERT INTO ISBS_ACCT_LEDGER_DET (ACCT_LEDGER_ID, CUST_ID, CREDIT_DEBIT_AMT, CREDIT_DEBIT_DTTM) VALUES ('ACC0000000001', 'CUST0000000001', -1000.25, TO_DATE('01-10-2008 11:00:00', 'DD-MM-YYYY HH24:MI:SS'));
INSERT INTO ISBS_ACCT_LEDGER_DET (ACCT_LEDGER_ID, CUST_ID, CREDIT_DEBIT_AMT, CREDIT_DEBIT_DTTM) VALUES ('ACC0000000002', 'CUST0000000002', -256.75, TO_DATE('01-10-2008 11:00:00', 'DD-MM-YYYY HH24:MI:SS'));
INSERT INTO ISBS_ACCT_LEDGER_DET (ACCT_LEDGER_ID, CUST_ID, CREDIT_DEBIT_AMT, CREDIT_DEBIT_DTTM) VALUES ('ACC0000000003', 'CUST0000000002', 100.25, TO_DATE('05-10-2008 11:00:00', 'DD-MM-YYYY HH24:MI:SS'));

内容的提问来源于stack exchange，提问作者S Ram Prakash