sql - 在 Oracle 中使用增强的聚合报告功能解决问题
问题描述
我正在使用 sqlplus (oracle)。我试图写一个声明来显示基于所有交易价值的账户价值变化
- 每个 customer_account_combination
- 每个客户
- 整个银行
此外,借方交易应为负,贷方交易应保持为正。我需要在 oracle 中使用增强的聚合报告功能来解决它。另外,应该有客户总数和总计。
这是我到目前为止得到的。
select wc.first_name "First", wc.surname "Last", wt.account_type "Act Type"
case when wt.transaction_type is NULL then NULL
when wt.transaction_type = 'D'then TO_CHAR(transaction_amount*-1, '$9,999.99')
else TO_CHAR(transaction_amount, '$9,999.99')
end "Total"
from wgb_customer wc join wgb_account wa on wa.customer_number = wc.customer_number join wgb_account_type wt on wa.account_type = wt.account_type
left outer join wgb_transaction wt on wt.customer_number = wa.customer_number
and wt.account_type = wa.account_type
order by 2,3,6;
但是,它不起作用,并且不显示客户总数或总计。请帮忙!
ERD
预期产出
解决方案
GROUP BY ROLLUP 是要走的路。它允许您创建所有可能的小计和总计。聚合数据后,您只需将连接保留到客户表以检索 first_name 和 last_name。确保进行左连接,以免丢失总计
--SAMPLE TABLES AND TEST DATA
CREATE TABLE WGB_TRANSACTION (
CUSTOMER_NUMBER VARCHAR2(7),
ACCOUNT_TYPE NUMBER(1),
TRANSACTION_AMOUNT NUMBER,
TRANSACTION_TYPE VARCHAR2(1)
);
CREATE TABLE WGB_CUSTOMER (
CUSTOMER_NUMBER VARCHAR2(7),
FIRST_NAME VARCHAR2(30),
SURNAME VARCHAR2(30)
);
INSERT INTO WGB_CUSTOMER VALUES ( '123', 'John','Smith');
INSERT INTO WGB_CUSTOMER VALUES ( '456', 'James','Anderson');
INSERT INTO WGB_TRANSACTION VALUES (123,1,100,'C');
INSERT INTO WGB_TRANSACTION VALUES (123,1,50,'D');
INSERT INTO WGB_TRANSACTION VALUES (123,2,100,'C');
INSERT INTO WGB_TRANSACTION VALUES (456,1,50,'C');
INSERT INTO WGB_TRANSACTION VALUES (456,1,100,'C');
INSERT INTO WGB_TRANSACTION VALUES (456,3,100,'D');
INSERT INTO WGB_TRANSACTION VALUES (456,1,50,'C');
--The Query
WITH BALANCES AS (
SELECT CUSTOMER_NUMBER, ACCOUNT_TYPE,
SUM(DECODE(TRANSACTION_TYPE,'D',-1,'C',1,0)*TRANSACTION_AMOUNT) BALANCE
FROM WGB_TRANSACTION WHERE TRANSACTION_TYPE IN ('C','D')
GROUP BY ROLLUP ( CUSTOMER_NUMBER, ACCOUNT_TYPE )
)
SELECT C.FIRST_NAME, C.SURNAME, B.ACCOUNT_TYPE, TO_CHAR(B.BALANCE,'$9,999.99')
FROM BALANCES B LEFT JOIN WGB_CUSTOMER C ON C.CUSTOMER_NUMBER=B.CUSTOMER_NUMBER;
--RESULT
FIRST_NAME SURNAME ACCOUNT_TYPE TO_CHAR(B.BALANCE,'$9,999.99')
John Smith (null) $150.00
John Smith 2 $100.00
John Smith 1 $50.00
James Anderson (null) $100.00
James Anderson 3 -$100.00
James Anderson 1 $200.00
(null) (null) (null) $250.00
推荐阅读
- regex - 如何使用正则表达式查找字符串的一部分?
- javascript - 如何在 jQuery 中仅定位当前悬停的元素
- opc-ua - 如何将客户端身份链接到 OPC-UA 中的 userRole
- sql - 基于查询结果的自定义验证规则
- redis - redis:内存有限时删除最旧的键
- postgresql - 安装 kong-ingress-controller 来管理 kubernetes 上的入口
- svn - 在 Assembla - SVN 中,只有管理员可以创建新的存储库吗?
- c++ - 创建和调用空函数
- hyperledger-fabric - Hyperledger Fabric:fabric-ca 请求注册失败并出现错误 [[{"code":20,"message":"Authorization failure"}]]
- java - Vertx 3.6.3:无法使用 PFX 选项启动 HTTPS 服务器