SELECT SUM(Debit)-SUM(Credit) AS 应收帐款 FROM JDT1 WHERE Account='113101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31' --结果是:77455
SELECT SUM(Debit)-SUM(Credit) AS 现金 FROM JDT1 WHERE Account='100101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31' --结果是:754133
SELECT SUM(Debit)-SUM(Credit) AS 主营业务成本 FROM JDT1 WHERE Account='540101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31' --结果是:3625现在我想得出如下结果:
科目名称 借方 贷方
应收帐款 77455
主营业务收入 77455
现金 754133
应收帐款 754133
主营业务成本 3625
库存商品 3625请问我的SQL该怎么写才能达到这个结果?
SELECT SUM(Debit)-SUM(Credit) AS 现金 FROM JDT1 WHERE Account='100101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31' --结果是:754133
SELECT SUM(Debit)-SUM(Credit) AS 主营业务成本 FROM JDT1 WHERE Account='540101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31' --结果是:3625现在我想得出如下结果:
科目名称 借方 贷方
应收帐款 77455
主营业务收入 77455
现金 754133
应收帐款 754133
主营业务成本 3625
库存商品 3625请问我的SQL该怎么写才能达到这个结果?
FROM JDT1 WHERE Account='113101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31'
union all
SELECT '主营业务收入' as 科目名称 , 0 as 借方,SUM(Debit)-SUM(Credit) as 贷方
FROM JDT1 WHERE Account='113101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31'
union all
SELECT '现金' as 科目名称 ,SUM(Debit)-SUM(Credit) ,0 FROM JDT1 WHERE Account='100101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31'
union all
SELECT '应收帐款' as 科目名称 ,0,SUM(Debit)-SUM(Credit) FROM JDT1 WHERE Account='100101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31'
union all
SELECT '主营业务成本',SUM(Debit)-SUM(Credit),0 FROM JDT1 WHERE Account='540101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31'
union all
SELECT '库存商品',0,SUM(Debit)-SUM(Credit) FROM JDT1 WHERE Account='540101' AND TaxDate BETWEEN '2009-1-1' AND '2009-1-31'