《最新sql查询金蝶科目余额表个期间的数据.doc》由会员分享，可在线阅读，更多相关《最新sql查询金蝶科目余额表个期间的数据.doc（110页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-datesql查询金蝶科目余额表个期间的数据sql查询金蝶科目余额表个期间的数据转载sql查询金蝶科目余额表个期间的数据 select null as QYDM, null as SBNF, null as SBYF, 本期发生_本年累计_科目余额表.会计年度 as会计年度, 本期发生_本年累计_科目余额表.会计期间 as 会计期间, 本期发生_本年累计_科目余额表.借贷方向
2、_1借_负1贷as 借贷方向_1借_负1贷, 本期发生_本年累计_科目余额表.科目级次 as科目级次, 本期发生_本年累计_科目余额表.明细科目_1是_0否 as明细科目_1是_0否, 本期发生_本年累计_科目余额表.科目编码 asKM, 本期发生_本年累计_科目余额表.科目名称 as科目代码, 本期发生_本年累计_科目余额表.本币借方发生额 as本期借方发生数BQJF, 本期发生_本年累计_科目余额表.本币贷方发生额 as本期贷方发生数BQDF, 本期发生_本年累计_科目余额表.本年本币借方发生额 as本年借方发生数BNJF, 本期发生_本年累计_科目余额表.本年本币贷方发生额 as本年贷方
3、发生数BNDF, 年初借方_年初贷方_余额_C.年初借方余额 as年初借方余额NCJF, 年初借方_年初贷方_余额_C.年初贷方余额 as年初贷方余额NCDF, 期初借方_期初贷方_余额_A.期初借方余额 as上期借方余额SQJF, 期初借方_期初贷方_余额_A.期初贷方余额 as上期贷方余额SQDF, 期末借方_期末贷方_余额_B.期末借方余额 as期末借方余额QMJF, 期末借方_期末贷方_余额_B.期末贷方余额 as期末贷方余额QMDF from ( select (convert(varchar(10),ltrim(rtrim(t_Balance.FYear) + - + conver
4、t(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) + - + co
5、nvert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID) + - + convert(varchar(20),ltrim(rtrim(t_Account.FNumber) ) as 本期索引码_年月币科目编码, (convert(varchar(10),ltrim(rtrim(t_Balance.FYear) + - + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID) + - + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID)
6、 ) 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.F
7、Name as 科目名称 , t_Balance.FDebit as 本币借方发生额, t_Balance.FCredit as 本币贷方发生额, t_Balance.FYtdDebit as 本年本币借方发生额, t_Balance.FYtdCredit as 本年本币贷方发生额, t_Balance.FAccountID as 科目内码_余额表, t_Account.FAccountID as 科目内码_科目表, t_Account.FDC as 借贷方向_1借_负1贷, t_Account.FLevel as 科目级次, t_Account.FDetail as 明细科目_1是_0否 f
8、romt_Balance left outer join t_Account ont_Balance.FAccountID = t_Account.FAccountID where t_Balance.FCurrencyID = 1 ) as 本期发生_本年累计_科目余额表 -左连接<期初借方和期初贷方>余额数据 left outerjoin ( select * from ( select (convert(varchar(10),ltrim(rtrim(t_Balance.FYear) + - + convert(varchar(10),ltrim(rtrim(t_Balanc
9、e.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_Ba
10、lance.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_Bala
11、nce.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
12、)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本期索引码_年月币科目内码, (conv
13、ert(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_Accoun
14、t.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科目默认为
15、借方;/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(r
16、trim(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),ltr
17、im(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
18、否, ( + 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.FBegin
19、Balance > 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本期索引码_
20、年月币科目内码, (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
21、会计期间, 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
22、.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 期初借方_期初贷方_余额 ) as 期初借方_期初贷方_余额_A on本期发生_本年累计_科目余额表.本期索引码_年月币科目内码 = 期初借方_期初贷方_余额_A.本期索引码_年月币科目内码 left outer
23、 join -左连接<期末借方和期末贷方>余额数据 ( 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本期索引码_年月币科目
24、内码, (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 会计期间,
25、 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 =
26、 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.FPeri
27、od) + - + 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.F
28、CurrencyID) + - + 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期末借方余额, (
29、- 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_Balanc
30、e.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本期索引码_
31、年月币科目内码, (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
32、会计期间, 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.
33、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
34、.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_Bal
35、ance.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期末借方
36、余额, ( - 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
37、t_Balance.FCurrencyID = 1 ) as 期末借方_期末贷方_余额 ) as期末借方_期末贷方_余额_B on本期发生_本年累计_科目余额表.本期索引码_年月币科目内码 = 期末借方_期末贷方_余额_B.本期索引码_年月币科目内码 -左连接<年初借方年初末贷方>余额数据 left outerjoin ( 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 本年索