create or replace view vatm as
select cardno,trxcode,count(*) hjbs, sum(trxamt) hjje from atmtotal group by cardno,trxcode;select a.cardno 卡号,b.hjbs 取款笔数,c.hjje 存款笔数,b.hjje 取款金额,c.hjje 存款金额,d.hjbs 转帐笔数,d.hjje 转帐金额,a.hjbs 合计笔数,a.hjje 合计金额
from
(
select cardno,count(*) hjbs,sum(hjje) hjje from vatm group by cardno
) a,
(
select cardno,hjbs,hjje from vatm where trxcode='4401'
) b,
(
select cardno,hjbs,hjje from vatm where trxcode='4402'
) c,
(
select cardno,hjbs,hjje from vatm where trxcode='4403'
) d
where a.cardno=b.cardno(+) and a.cardno=c.cardno(+) and a.cardno=d.cardno(+);
select cardno,trxcode,count(*) hjbs, sum(trxamt) hjje from atmtotal group by cardno,trxcode;select a.cardno 卡号,b.hjbs 取款笔数,c.hjje 存款笔数,b.hjje 取款金额,c.hjje 存款金额,d.hjbs 转帐笔数,d.hjje 转帐金额,a.hjbs 合计笔数,a.hjje 合计金额
from
(
select cardno,count(*) hjbs,sum(hjje) hjje from vatm group by cardno
) a,
(
select cardno,hjbs,hjje from vatm where trxcode='4401'
) b,
(
select cardno,hjbs,hjje from vatm where trxcode='4402'
) c,
(
select cardno,hjbs,hjje from vatm where trxcode='4403'
) d
where a.cardno=b.cardno(+) and a.cardno=c.cardno(+) and a.cardno=d.cardno(+);
解决方案 »
- oracle10安装 There were errors loading the aggregates
- 求一SQL获取表中,上下行某一字段的差值
- oracle 9i R2 可以对数据库做整体加密吗?
- oracle中的完全恢复和不完全恢复有什么区别?如何确定可以采用哪种恢复方式?
- 一个简单问题?
- 基础问题不懂; is 和 as区别? 联机帮助?
- 创建用户
- 语句出错:ALTER USER YOU IDENTIFIED BY 111; 新手提问!
- 关于cursor的用法,我觉得没问题,可是就是通不过,查看相关文档,也没有明确的说法
- oracle8i 在win98 P4下安装的问题
- 执行job出错:ORA-12011: 无法执行 1 作业。。。。
- oracle 9i 怎么实现表间的联系
帐笔数,d.hjje 转帐金额,a.hjbs 合计笔数,a.hjje 合计金额
2 from
3 (
4 select cardno,count(*) hjbs,sum(hjje) hjje from vatm group by cardno
5 ) a,
6 (
7 select cardno,hjbs,hjje from vatm where trxcode='4401'
8 ) b,
9 (
10 select cardno,hjbs,hjje from vatm where trxcode='4402'
11 ) c,
12 (
13 select cardno,hjbs,hjje from vatm where trxcode='4403'
14 ) d
15 where a.cardno=b.cardno(+) and a.cardno=c.cardno(+) and a.cardno=d.cardno(+);卡号 取款笔数 存款笔数 取款金额 存款金额 转帐笔数 转帐金额 合计笔数 合计金额
--------- --------- --------- --------- --------- --------- --------- --------- ---------
042030003 1 50000 30000 50000 2 80000
042030004 2 5000 1 5000
042030005 60000 60000 1 30000 2 90000SQL>
select cardno,trxcode,count(*) hjbs, sum(trxamt) hjje from atmtotal group by cardno,trxcode
select a.cardno 卡号,b.hjbs 取款笔数,c.hjbs 存款笔数,b.hjje 取款金额,c.hjje 存款金额,d.hjbs 转帐笔数,d.hjje 转帐金额,a.hjbs 合计笔数,a.hjje 合计金额
from
(
select cardno,count(*) hjbs,sum(hjje) hjje from vatm group by cardno --在此改sum(hjje)
) a,
(
select cardno,hjbs,hjje from vatm where trxcode='4401'
) b,
(
select cardno,hjbs,hjje from vatm where trxcode='4402'
) c,
(
select cardno,hjbs,hjje from vatm where trxcode='4403'
) d
where a.cardno=b.cardno(+) and a.cardno=c.cardno(+) and a.cardno=d.cardno(+);不知楼主合计金额是如何计算的,如结果不对,楼主可在结果集a中修改