网址:http://blog.sina.com.cn/s/blog_15b9821340102wbe5.html
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 集团组织机构内码
fromt_Balance left outer join t_Account ont_Balance.FAccountID = t_Account.FAccountID
orderby
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 集团组织机构内码
fromt_Balance left outer join t_Account ont_Balance.FAccountID = t_Account.FAccountID
where
t_Balance.FYear = 2016and
t_Balance.FPeriod = 3 and
t_Balance.FCurrencyID = 1
orderby
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 集团组织机构内码
fromt_Balance left outer join t_Account ont_Balance.FAccountID = t_Account.FAccountID
where
t_Balance.FCurrencyID = 1
orderby
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 leftouter 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 = 1AND
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 leftouter 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 = 1AND
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 leftouter 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 leftouter 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 leftouter 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 = 1AND
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 leftouter 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 = 1AND
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 leftouter 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 leftouter 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 leftouter 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 = 1AND
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 leftouter 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 = 1AND
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 leftouter 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 leftouter 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 会计年度,
会计期间,
科目编码
查询结果:
---------------------