下面有一条语句是这样的
select COUNT(*) as 服务次数,SUM(new_actualfee) as 服务费,SUM(new_travelcost) as 差旅费,SUM(new_feeofworkovertime) as 加班费,(SUM(new_travelcost)+SUM(new_feeofworkovertime)+SUM(new_actualfee)) 小计
from incident
where new_othersubengineer like '%测试员2%'
group by new_actualfee,new_travelcost,new_feeofworkovertime
查询结果:
服务次数 服务费 差旅费 加班费 小计
1 1.00 2.00 2.00 5.00
1 0.00 1.00 2.00 3.00要把查询结果变成以下的结果集,需要将语句怎么改造?
服务次数 服务费 差旅费 加班费 小计
2 1.00 3.00 4.00 8.88
select COUNT(*) as 服务次数,SUM(new_actualfee) as 服务费,SUM(new_travelcost) as 差旅费,SUM(new_feeofworkovertime) as 加班费,(SUM(new_travelcost)+SUM(new_feeofworkovertime)+SUM(new_actualfee)) 小计
from incident
where new_othersubengineer like '%测试员2%'
group by new_actualfee,new_travelcost,new_feeofworkovertime
查询结果:
服务次数 服务费 差旅费 加班费 小计
1 1.00 2.00 2.00 5.00
1 0.00 1.00 2.00 3.00要把查询结果变成以下的结果集,需要将语句怎么改造?
服务次数 服务费 差旅费 加班费 小计
2 1.00 3.00 4.00 8.88
as [加班费],sum(小计) as [小计] from (
select COUNT(*) as 服务次数,SUM(new_actualfee) as 服务费,SUM(new_travelcost) as 差旅费,SUM(new_feeofworkovertime) as 加班费,(SUM(new_travelcost)+SUM(new_feeofworkovertime)+SUM(new_actualfee)) 小计
from incident
where new_othersubengineer like '%测试员2%'
group by new_actualfee,new_travelcost,new_feeofworkovertime ) t
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
from incident
where new_othersubengineer like '%测试员2%'
group by new_actualfee,new_travelcost,new_feeofworkovertime你查出来就是一条数据 所以 group by new_actualfee,new_travelcost,new_feeofworkovertime 可以去掉
create view myview
as
select COUNT(*) as 服务次数,SUM(new_actualfee) as 服务费,SUM(new_travelcost) as 差旅费,SUM(new_feeofworkovertime) as 加班费,(SUM(new_travelcost)+SUM(new_feeofworkovertime)+SUM(new_actualfee)) 小计
from incident
where new_othersubengineer like '%测试员2%'
group by new_actualfee,new_travelcost,new_feeofworkovertime然后对视图做分组查询