sql查询金蝶科目余额表个期间的数据.doc

转载sql查询金蝶科目余额表个期间的数据 select null as QYDM, null as SBNF, null as SBYF, 本期发生_本年累计_科目余额表.会计年度 as会计年度, 本期发生_本年累计_科目余额表.会计期间 as 会计期间, 本期发生_本年累计_科目余额表.借贷方向_1借_负1贷as 借贷方向_1借_负1贷, 本期发生_本年累计_科目余额表.科目级次 as科目级次, 本期发生_本年累计_科目余额表.明细科目_1是_0否 as明细科目_1是_0否, 本期发生_本年累计_科目余额表.科目编码 asKM, 本期发生_本年累计_科目余额表.科目名称 as科目代码, 本期发生_本年累计_科目余额表.本币借方发生额 as本期借方发生数BQJF, 本期发生_本年累计_科目余额表.本币贷方发生额 as本期贷方发生数BQDF, 本期发生_本年累计_科目余额表.本年本币借方发生额 as本年借方发生数BNJF, 本期发生_本年累计_科目余额表.本年本币贷方发生额 as本年贷方发生数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) + - + 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 本期索引码_年月币科目编码, (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_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否 fromt_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_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 期初借方_期初贷方_余额 ) as 期初借方_期初贷方_余额_A on本期发生_本年累计_科目余额表.本期索引码_年月币科目内码 = 期初借方_期初贷方_余额_A.本期索引码_年月币科目内码 left outer 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本期索引码_年月币科目内码, (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 期末借方_期末贷方_余额 ) 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 本年索引码_年_币_科目编码, 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