22,210
社区成员
发帖
与我相关
我的任务
分享
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) + '月合计'
) t
order by maketime , case ActionType when '月合计' then 3 when '日合计' then 2 else 1 end , ActionType
drop table tb
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 , ActionType
drop 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 行)
*/
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