分享一个sql查询
在实际开发过程中sql 的书写是一个硬本领,写好sql能节省很多业务代码,下面一个例子:
先创建数据表:
1 create table coreOperate( 2 cust_no varchar(20), 3 operate_type char(2), 4 brch_no varchar2(20), 5 operate_money number(15,2) 6 7 ); 8 comment on colum coreOperate.cust_no is '客户号' 9 comment on colum coreOperate.operate_type is '操作类型:01 存,02 取' 10 comment on colum coreOperate.brch_no is '机构号' 11 comment on colum coreOperate.operate_money is '操作金额' 12 commit;
要求查询出客户的所有余额,因为存和取都在同一张数据表中,并且客户号不同,首先我们可以查询出所有客户所有的存和取金额:
select cust_no,operate_type,coalesce(sum(operate_money),0.00) money group by cust_no,operate_type
然后对同一客户进行操作:
select cust_no,sum(case when t.operate_type='01' then money else -money end ) as leaveMoney from ( select cust_no,operate_type,coalesce(sum(operate_money),0.00) money group by cust_no,operate_type )t group by cust_no
记录成长,今生无悔