年月日统计的问题

Question

select * from 
(
  select convert(varchar(10),maketime,120) maketime, ActionType,TotalMoney from tb
  union all
  select convert(varchar(10),maketime,120) maketime, ActionType ='日合计' , TotalMoney= sum(case ActionType when '收款' then TotalMoney else -TotalMoney end) from tb group by convert(varchar(10),maketime,120)
  union all
  select convert(varchar(7),maketime,120) + '月合计' maketime, ActionType = '' , TotalMoney= sum(case ActionType when '收款' then TotalMoney else -TotalMoney end) from tb group by convert(varchar(7),maketime,120) + '月合计'
  union all
  select convert(varchar(4),maketime,120) + '年合计' maketime, ActionType = '' , TotalMoney= sum(case ActionType when '收款' then TotalMoney else -TotalMoney end) from tb group by convert(varchar(4),maketime,120) + '年合计'
) t
order by maketime , case ActionType when '年合计' then 4 when '月合计' then 3 when '日合计' then 2 else 1 end , ActionType

Answer 1

Create table tb
( 
MakeTime DateTime, 
ActionType varchar(20), 
BillType varchar(10), 
SmallType varchar(50), 
BillID varchar(20), 
TotalMoney float, 
OrganizationID int, 
EmployeePP varchar(20) 
) 
Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-1-1','收款','经营活动','商品销售','SPXS2008010101',100,1,'小刘') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-1-1','收款','经营活动','商品销售','SPXS2008010102',150,1,'小王') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-1-1','付款','经营活动','商品采购','SPCG2008010101',200,1,'小王') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-1-2','付款','经营活动','商品采购','SPCG2008010201',100,1,'小王') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-1-2','收款','经营活动','商品销售','SPXS2008010201',100,1,'小吴') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-1-2','付款','经营活动','费用报销','FYBX2008010201',50,1,'小吴') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-2-3','付款','经营活动','商品采购','SPCG2008010301',150,1,'小王') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-2-3','收款','经营活动','商品销售','SPXS2008010301',500,1,'小吴') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-4-3','付款','经营活动','费用报销','FYBX2008010301',150,1,'小吴') 
Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-4-4','收款','经营活动','商品销售','SPXS2008010401',200,1,'小刘') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-4-4','收款','经营活动','商品销售','SPXS2008010402',150,1,'小王') Insert into tb(MakeTime, ActionType, BillType, SmallType, BillID, TotalMoney, OrganizationID, EmployeePP) 
Values('2008-4-4','付款','经营活动','商品采购','SPCG2008010401',100,1,'小王') 
go
select * from 
(
  select convert(varchar(10),maketime,120) maketime, ActionType,TotalMoney from tb
  union all
  select convert(varchar(10),maketime,120) maketime, ActionType ='日合计' , TotalMoney= sum(case ActionType when '收款' then TotalMoney else -TotalMoney end) from tb group by convert(varchar(10),maketime,120)
  union all
  select convert(varchar(7),maketime,120) + '月合计' maketime, ActionType = '' , TotalMoney= sum(case ActionType when '收款' then TotalMoney else -TotalMoney end) from tb group by convert(varchar(7),maketime,120) + '月合计'
  union all
  select convert(varchar(4),maketime,120) + '年合计' maketime, ActionType = '' , TotalMoney= sum(case ActionType when '收款' then TotalMoney else -TotalMoney end) from tb group by convert(varchar(4),maketime,120) + '年合计'
) t
order by maketime , case ActionType when '月合计' then 3 when '日合计' then 2 else 1 end , ActionTypedrop table tb/*
maketime      ActionType           TotalMoney                                            
------------- -------------------- ----------------------------------------------------- 
2008-01-01    付款                   200.0
2008-01-01    收款                   100.0
2008-01-01    收款                   150.0
2008-01-01    日合计                  50.0
2008-01-02    付款                   100.0
2008-01-02    付款                   50.0
2008-01-02    收款                   100.0
2008-01-02    日合计                  -50.0
2008-01月合计                         0.0
2008-02-03    付款                   150.0
2008-02-03    收款                   500.0
2008-02-03    日合计                  350.0
2008-02月合计                         350.0
2008-04-03    付款                   150.0
2008-04-03    日合计                  -150.0
2008-04-04    付款                   100.0
2008-04-04    收款                   150.0
2008-04-04    收款                   200.0
2008-04-04    日合计                  250.0
2008-04月合计                         100.0
2008年合计                            450.0（所影响的行数为 21 行）*/