licheng700

BlogJava 首页 新随笔 联系 聚合 管理
  26 Posts :: 5 Stories :: 5 Comments :: 1 Trackbacks

select  n_accountid,accountNO,accountName ,
sum(decode(n_direction,1,n_amount, 0)) amountOfDebit,
sum(decode(n_direction,2,n_amount, 0)) amountOfCredit,
sum(n_amount) amount,
sum(decode(n_direction,1,1, 0)) numOfDebitTrans,
sum(decode(n_direction,2,1, 0)) numOfCreditTrans,
COUNT(n_direction) nums
from

(select a.n_accountid n_accountid ,
a.n_amount n_amount,a.n_direction n_direction,
b.s_accountno accountNO,b.s_accountname  accountName from

(select n_id ,n_accountid,n_currencytype,n_amount,n_direction,dt_modifytime from bs_accthistransinfo where 1=1
 and to_char(dt_modifytime,'yyyy-mm-dd')>='2005-01-01'
 and to_char(dt_modifytime,'yyyy-mm-dd')<='2006-01-01'
 union all
select n_id ,n_accountid,n_currencytype,n_amount,n_direction,dt_modifytime from bs_acctcurtransinfo where 1=1
 and to_char(dt_modifytime,'yyyy-mm-dd')>='2005-01-01'
 and to_char(dt_modifytime,'yyyy-mm-dd')<='2006-01-01'
)a,  

(select n_id,s_accountno,s_accountname,n_currencytype from bs_bankaccountinfo where
    n_ischeck=1
 and n_accountstatus= 1)
 b
 where a.n_currencytype=b.n_currencytype  and a.n_accountid=b.n_id
)
c
group by n_accountid,accountNO,accountName


 

posted on 2005-08-25 16:31 小海船 阅读(171) 评论(1)  编辑  收藏

评论

# re: 一段SQL 2005-08-25 17:02 小海船
select n_accountid,accountNO,accountName ,
sum(decode(n_direction,1,n_amount, 0)) amountOfDebit,
sum(decode(n_direction,2,n_amount, 0)) amountOfCredit,
sum(n_amount) amount,
sum(decode(n_direction,1,1, 0)) numOfDebitTrans,
sum(decode(n_direction,2,1, 0)) numOfCreditTrans,
COUNT(n_direction) nums
from
(select a.n_id n_id,a.n_accountid n_accountid ,a.n_currencytype n_currencytype,
a.n_amount n_amount,a.n_direction n_direction,a.dt_modifytime dt_modifytime,
b.s_accountno accountNO,b.s_accountname accountName from
(select n_id ,n_accountid,n_currencytype,n_amount,n_direction,dt_modifytime from bs_accthistransinfo where 1=1
union all
select n_id ,n_accountid,n_currencytype,n_amount,n_direction,dt_modifytime from bs_acctcurtransinfo where 1=1)
a,
(select * from bs_bankaccountinfo where
n_accountstatus= 1
and n_ischeck=1) b where a.n_currencytype=b.n_currencytype and a.n_accountid=b.n_id and
to_char(a.dt_modifytime,'yyyy-mm-dd')>='2005-01-01' and
to_char(a.dt_modifytime,'yyyy-mm-dd')<='2006-01-01'
)c group by n_accountid,accountNO,accountName  回复  更多评论
  


只有注册用户登录后才能发表评论。


网站导航: