打开APP
userphoto
未登录

开通VIP，畅享免费电子书等14项超值服

开通VIP

首页

好书

留言交流

下载APP

联系客服
sql查询金蝶科目余额表中的数据(t_Balance/t_Account)
userphoto 关注
sql查询金蝶科目余额表中的数据(t_Balance/t_Account)
--连接关系表-1.科目余额表+会计科目表-1.1所有数据
   select 
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,     
      t_Balance.FAccountID as 科目内码_余额表,
      t_Account.FAccountID as 科目内码_科目表,
      t_Account.FNumber as 科目编码,
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FDetail as 明细科目_1是_0否,
      t_Account.FName as 科目名称 ,  
      t_Account.FLevel as 科目级次,
      t_Account.FGroupID as 科目类别内码,
      t_Balance.FCurrencyID as 币别内码,
      t_Balance.FDetailID as 核算项目使用内码,
      t_Balance.FBeginBalance as 本币期初余额,
      t_Balance.FBeginBalanceFor as 原币期初余额,
      t_Balance.FDebit as 本币借方发生额,
      t_Balance.FDebitFor as 原币借方发生额,
      t_Balance.FCredit as 本币贷方发生额,
      t_Balance.FCreditFor as 原币贷方发生额,
      t_Balance.FYtdDebit as 本年本币借方发生额,
      t_Balance.FYtdDebitFor as 本年原币借方发生额,
      t_Balance.FYtdCredit as 本年本币贷方发生额,
      t_Balance.FYtdCreditFor as 本年原币贷方发生额,
      t_Balance.FEndBalance as 本币期末余额,
      t_Balance.FEndBalanceFor as 原币期末余额,
      t_Balance.FFrameWorkID as 集团组织机构内码     
   from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
   order by 
      t_Account.FNumber, 
      t_Balance.FPeriod
查询结果如下:


---------------------
--连接关系表-1.科目余额表+会计科目表-1.2.1整理格式<字段的先后顺序>
--会计年度=2016
--会计期间=3
--币别=人民币
   select 
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,     
      t_Account.FNumber as 科目编码,    
      t_Account.FName as 科目名称 ,  
      t_Balance.FDebit as 本币借方发生额,
      t_Balance.FCredit as 本币贷方发生额,
      t_Balance.FYtdDebit as 本年本币借方发生额,
      t_Balance.FYtdCredit as 本年本币贷方发生额,
      t_Balance.FBeginBalance as 本币期初余额,
      t_Balance.FEndBalance as 本币期末余额,
      t_Balance.FAccountID as 科目内码_余额表,
      t_Account.FAccountID as 科目内码_科目表,
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FDetail as 明细科目_1是_0否,
      t_Account.FLevel as 科目级次,
      t_Account.FGroupID as 科目类别内码,
      t_Balance.FCurrencyID as 币别内码,
      t_Balance.FDetailID as 核算项目使用内码,
      t_Balance.FBeginBalanceFor as 原币期初余额,
      t_Balance.FDebitFor as 原币借方发生额,
      t_Balance.FCreditFor as 原币贷方发生额,
      t_Balance.FYtdDebitFor as 本年原币借方发生额,
      t_Balance.FYtdCreditFor as 本年原币贷方发生额,
      t_Balance.FEndBalanceFor as 原币期末余额,
      t_Balance.FFrameWorkID as 集团组织机构内码     
   from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
   where
      t_Balance.FYear = 2016 and 
      t_Balance.FPeriod = 3  and
      t_Balance.FCurrencyID = 1
   order by 
      t_Account.FNumber, 
      t_Balance.FPeriod

结果如下图:


---------------------
--连接关系表-1.科目余额表+会计科目表-1.2.2增加每个月的科目余额表的索引码
--索引码用根据 :年+月+科目编码(或内码)组成
   select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear))) + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod))) +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,     
      t_Account.FNumber as 科目编码,    
      t_Account.FName as 科目名称 ,  
      t_Balance.FDebit as 本币借方发生额,
      t_Balance.FCredit as 本币贷方发生额,
      t_Balance.FYtdDebit as 本年本币借方发生额,
      t_Balance.FYtdCredit as 本年本币贷方发生额,
      t_Balance.FBeginBalance as 本币期初余额,
      t_Balance.FEndBalance as 本币期末余额,
      t_Balance.FAccountID as 科目内码_余额表,
      t_Account.FAccountID as 科目内码_科目表,
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FDetail as 明细科目_1是_0否,
      t_Account.FLevel as 科目级次,
      t_Account.FGroupID as 科目类别内码,
      t_Balance.FCurrencyID as 币别内码,
      t_Balance.FDetailID as 核算项目使用内码,
      t_Balance.FBeginBalanceFor as 原币期初余额,
      t_Balance.FDebitFor as 原币借方发生额,
      t_Balance.FCreditFor as 原币贷方发生额,
      t_Balance.FYtdDebitFor as 本年原币借方发生额,
      t_Balance.FYtdCreditFor as 本年原币贷方发生额,
      t_Balance.FEndBalanceFor as 原币期末余额,
      t_Balance.FFrameWorkID as 集团组织机构内码     
   from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
   where 
      t_Balance.FCurrencyID = 1
   order by 
      t_Account.FNumber, 
      t_Balance.FPeriod
查询结果:


---------------------

--1>.*********************将<期初余额>,拆分为<期初借方余额>和<期初贷方余额>
--连接关系表-1.科目余额表+会计科目表-1.3.1将<本币期初余额>,拆分为  <期初借方余额>和<期初贷方余额>
--t_Balance.FCurrencyID =1 表示币别内码等于1,即人民币
--t_Account.FDC = 1 表示 科目的默认方向为:借方   ,将余额转移到借方期初<如果正数都放借方,/如果是负数放贷方>即where 中将t_Account.FDC = 1结合:t_Balance.FBeginBalance>=0  和<0
--t_Account.FDC = -1 表示 科目的默认方向为:贷方  ,将余额转移到贷方期初<改为凡是负数都放贷方并转为正数,如果是正数,放借方>即where 中将t_Account.FDC = -1结合:t_Balance.FBeginBalance<=0 和 >0

--以下为  会计科目方向为 <借方>的语句

 select *
 from 
  (
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
    ( + t_Balance.FBeginBalance ) as 期初借方余额,
      null as 期初贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初 大于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = 1 AND 
      t_Balance.FBeginBalance >= 0 )and 
      t_Balance.FCurrencyID = 1 
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
      null as 期初借方余额,    
    ( - t_Balance.FBeginBalance ) as 期初贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初 小于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = 1 AND 
      t_Balance.FBeginBalance < 0 )and 
      t_Balance.FCurrencyID = 1 
  --以下为  会计科目方向为 <贷方>的语句
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
    ( + t_Balance.FBeginBalance ) as 期初借方余额,     
      null as 期初贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初 大于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = -1 AND 
      t_Balance.FBeginBalance > 0 )and 
      t_Balance.FCurrencyID = 1 
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
      null as 期初借方余额,      
    ( - t_Balance.FBeginBalance ) as 期初贷方余额 
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初 小于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = -1 AND 
      t_Balance.FBeginBalance <= 0 )and 
      t_Balance.FCurrencyID = 1 
  ) as 期初借方_期初贷方_余额
order by 
   会计年度,
   会计期间,
   科目编码

查询结果:



---------------------

--2>.*********************将<年初余额>,拆分为<年初借方余额>和<年初贷方余额>
--连接关系表-1.科目余额表+会计科目表-1.3.1将<本币年初余额>,拆分为  <年初借方余额>和<年初贷方余额>
--t_Balance.FCurrencyID =1 表示币别内码等于1,即人民币
--t_Account.FDC = 1 表示 科目的默认方向为:借方   ,将余额转移到借方期初<如果正数都放借方,/如果是负数放贷方>即where 中将t_Account.FDC = 1结合:t_Balance.FBeginBalance>=0  和<0
--t_Account.FDC = -1 表示 科目的默认方向为:贷方  ,将余额转移到贷方期初<改为凡是负数都放贷方并转为正数,如果是正数,放借方>即where 中将t_Account.FDC = -1结合:t_Balance.FBeginBalance<=0 和 >0
--和上面的代码一样,只是  条件中,增加了一个<期间= 1>的条件
--以下为  会计科目方向为 <借方>的语句

 select *
 from 
  (
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本年索引码_年_币_科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
    ( + t_Balance.FBeginBalance ) as 年初借方余额,
      null as 年初贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初 大于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = 1 AND 
      t_Balance.FBeginBalance >= 0 )and 
      t_Balance.FCurrencyID = 1 
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本年索引码_年_币_科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
      null as 年初借方余额,    
    ( - t_Balance.FBeginBalance ) as 年初贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初 小于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = 1 AND 
      t_Balance.FBeginBalance < 0 )and 
      t_Balance.FCurrencyID = 1 
  --以下为  会计科目方向为 <贷方>的语句
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本年索引码_年_币_科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
    ( + t_Balance.FBeginBalance ) as 年初借方余额,     
      null as 年初贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初 大于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = -1 AND 
      t_Balance.FBeginBalance > 0 )and 
      t_Balance.FCurrencyID = 1 
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本年索引码_年_币_科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本年索引码_年_币_科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
      null as 年初借方余额,      
    ( - t_Balance.FBeginBalance ) as 年初贷方余额 
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初 小于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = -1 AND 
      t_Balance.FBeginBalance <= 0 )and 
      t_Balance.FCurrencyID = 1 
  ) as 年初借方_年初贷方_余额
where 会计期间 = 1
order by 
   会计年度,
   会计期间,
   科目编码
查询结果:



---------------------


--3>.*********************将<期末余额>,拆分为<期末借方余额>和<期末贷方余额>
--连接关系表-1.科目余额表+会计科目表-1.3.1将<本币期期末额>,拆分为  <期末借方余额>和<期末贷方余额>
--t_Balance.FCurrencyID =1 表示币别内码等于1,即人民币
--t_Account.FDC = 1 表示 科目的默认方向为:借方   ,将余额转移到借方期初<如果正数都放借方,/如果是负数放贷方>即where 中将t_Account.FDC = 1结合:t_Balance.FEndBalance>=0  和<0
--t_Account.FDC = -1 表示 科目的默认方向为:贷方  ,将余额转移到贷方期初<改为凡是负数都放贷方并转为正数,如果是正数,放借方>即where 中将t_Account.FDC = -1结合:t_Balance.FEndBalance<=0 和 >0

--以下为  会计科目方向为 <借方>的语句

 select *
 from 
  (
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
    ( + t_Balance.FEndBalance ) as 期末借方余额,
      null as 期末贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初 大于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = 1 AND 
      t_Balance.FEndBalance >= 0 )and 
      t_Balance.FCurrencyID = 1 
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
      null as 期末借方余额,    
    ( - t_Balance.FEndBalance ) as 期末贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = 1 科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初 小于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = 1 AND 
      t_Balance.FEndBalance < 0 )and 
      t_Balance.FCurrencyID = 1 
  --以下为  会计科目方向为 <贷方>的语句
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
    ( + t_Balance.FEndBalance ) as 期末借方余额,     
      null as 期末贷方余额
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初 大于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = -1 AND 
      t_Balance.FEndBalance > 0 )and 
      t_Balance.FCurrencyID = 1 
  union all
  select 
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as 本期索引码_年月币科目内码,
      (convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))  + '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))  +  '-' +  convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))  +  '-' +  convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )   as 本期索引码_年月币科目编码,
      t_Balance.FYear as 会计年度,
      t_Balance.FPeriod as 会计期间,   
      t_Account.FNumber as 科目编码,   
      t_Account.FName as 科目名称 ,    
      t_Account.FDC as 借贷方向_1借_负1贷,
      t_Account.FLevel as 科目级次,
      t_Account.FDetail as 明细科目_1是_0否,
      null as 期末借方余额,      
    ( - t_Balance.FEndBalance ) as 期末贷方余额 
  from t_Balance left outer join t_Account  on t_Balance.FAccountID = t_Account.FAccountID
  --1.t_Account.FDC = -1 科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初 小于 0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
  where (t_Account.FDC = -1 AND 
      t_Balance.FEndBalance <= 0 )and 
      t_Balance.FCurrencyID = 1 
  ) as 期末借方_期末贷方_余额

order by 会计年度,
     会计期间,
     科目编码
查询结果:
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。
打开APP，阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序，算一算2024你的财运
[转载]sql查询用友
JS去掉空格
如何同时对多个表或列操作
身份证、组织机构代码及营业执照号校验函数
如何利用Convert将时间转换为字符串形式!
Oracle字符串函数
更多类似文章 >>
生活服务
热点新闻
留言交流
回顶部
联系我们
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权！
如果VIP功能使用有故障，
可点击这里联系客服！

联系客服