首页 新闻 搜索 专区 学院

如何统计出费用?

0
悬赏园豆:20 [待解决问题]

数据库有一张费用表,需要根据车牌号码和时间分组,并且统计出费用?
数据字段:

需要实现这张效果的

自己写的sql:
select CarNumber,CostType,CostMoney,ReturnTime from
(select case when CarNumber is not null then CarNumber else '总计' end CarNumber,
isnull(convert(varchar(100),ReturnTime,120),0) as ReturnTime,
case when sum(cast(FuelCosts as int)) is not null then sum(cast(FuelCosts as int)) else null end 加油费,
case when sum(cast(RepairCosts as int)) is not null then sum(cast(RepairCosts as int)) else null end 维修费 ,
case when sum(cast(InsuranceCosts as int)) is not null then sum(cast(InsuranceCosts as int)) else null end 保险费 ,
case when sum(cast(CorporateCard as int)) is not null then sum(cast(CorporateCard as int))else null end 公务卡 ,
case when sum(cast(YuetongCard as int)) is not null then sum(cast(YuetongCard as int)) else null end 粤通卡,
case when sum(cast(MonthlyCosts as int)) is not null then sum(cast(MonthlyCosts as int)) else null end 月保费,
case when sum(cast(ProbationCosts as int)) is not null then sum(cast(ProbationCosts as int))else null end 临保费 ,
case when sum(cast(CarboatCosts as int)) is not null then sum(cast(CarboatCosts as int))else null end 车船费,
case when sum(cast(CleaningCosts as int)) is not null then sum(cast(CleaningCosts as int)) else null end 保洁费
FROM CarCosts group by CarNumber,ReturnTime with rollup ) as a
unpivot(CostMoney for CostType in (加油费,维修费,保险费,公务卡,粤通卡,月保费,临保费,车船费,保洁费)) as b
效果图:

求博客园的老哥帮帮忙

墨染白青的主页 墨染白青 | 初学一级 | 园豆:98
提问于:2019-04-09 10:55
< >
分享
所有回答(1)
0

先按照 车牌 日期 费用类型 三个条件分组 算出结果a
再对a进行数据加工

通信的搞程序 | 园豆:1729 (小虾三级) | 2019-04-09 22:10
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册