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(+);
解决方案 »
- ORACLE 把SYSBASE数据库中的数据导入到ORACLE数据库出现乱码。。
- ASP.NET 调用ORACLE存储过程 传递参数为数组
- 1位数月份的问题(在线等待,马上结账送分)
- oracle 10g比 9i有什么优势吗
- 请教数据库导入优化问题
- 急,这条语句怎么写?
- oracle 事务处理(给一点建议的都有分)
- 菜鸟问题!如何把一个库里面的所有表和数据导出,到另外一台机器上,类似于复制的功能!在线等,解决马上给分
- 连接Oracle 9i出现以下错误:ORA-12154:TNS:无法处理服务名
- 请问哪位大虾知道oracle9i的初始帐号和密码?
- 执行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中修改