我想要一年的明智数据 [英] I want to month wise data of year

问题描述

我有两张桌子像

1张表

i have two table like
1 table

Date         DistId BranchID CreditAmount
01/01/2016   101       1        5000
02/01/2016   101       1        5000
01/02/2016   101       1        6000
01/01/2016   101       2        700
01/01/2016   102       1        400

2 table

2 table

Date        DistId  BranchId  DebitAmount
01/01/2016   101       1        6000
02/01/2016   101       1        7000
01/02/2016   101       1        9000
01/01/2016   101       2        500
01/01/2016   102       1        400

i希望如下所示

i want to like as under below

Date        DistId BranchId  CreditAmount DebitAmount Balance AmountType
01/01/2016  101     1           10000      13000       3000      Dr
01/02/2016  101     1           16000      22000       6000      Dr

1 / 12/2016



我的尝试：

as 1/12/2016

What I have tried:

select a.Date, a.DistID, a.BranchId, sum(CreditAmount)
from t a;

推荐答案

SELECT a.Date, a.DistID, a.BranchId, SUM(a.CreditAmount) AS CreditAmount, NULL AS DebitAmount
FROM a
GROUP BY a.Date, a.DistID, a.BranchId
UNION
SELECT b.Date, b.DistID, b.BranchId, NULL, SUM(b.DebitAmount) 
FROM b
GROUP BY b.Date, b.DistID, b.BranchId

这篇关于我想要一年的明智数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！