!!!!!!!求一条SQL语句!!!!!!!在线等各位达人!!!!!!!!
表1. 车辆表 暂叫 CarAccount 字段有:
1)DeptID ... 部门ID
2)CarID ... 车辆ID
3)OilType ... 加油类型(只有0和1两种)
......(其它略过)
表2. 车辆加油表 暂叫 CarOil 字段有:
1)CarID ... 车辆ID
2)OilFare ... 加油费
3)OilDate ... 加油时间
......(其它略过)
表3. 车辆里程表 暂叫 CarMile 字段有:
1)CarID ... 车辆ID
2)CarMile ... 登记里程数
3)CarDate ... 登记时间
......(其它略过)
此时我想得到这样的结果:
_________________________________________________________________________
| | | 汽油车(0) | 柴油车(1) |_______|_______|____________________________|__________________________|
| 部门 | 总台数| 台数 | 里程 | 用油 | 百公里| 台数 | 里程 | 用油|百公里|
-------------------------------------
注:百公里=用油*100/里程
请哪位达人.给小弟指点一二.感激不尽!!!
[解决办法]
乱写:
select A.DeptID,A.总台数,
(
select Count(1),sum(A3.CarMile) 里程,sum(A2.OilFare) 用油sum(A3.OilFare)*100/,sum(A2.CarMile) 百公里
from
(select CarID from CarAccount where DeptID=A.DeptID and OilType=0) A1,
CarOil A2,
CarMile A3
where A1.CarID=A2.CarID and A1.CarID=A3.CarID
),
(
select Count(1),sum(B3.CarMile) 里程,sum(B2.OilFare) 用油sum(B3.OilFare)*100/,sum(B2.CarMile) 百公里
from
(select CarID from CarAccount where DeptID=A.DeptID and OilType=1) B1,
CarOil B2,
CarMile B3
where B1.CarID=B2.CarID and B1.CarID=B3.CarID
)
from
(
select DeptID,count(1) 总台数
from CarAccount
Group By DepartID
) A
[解决办法]
select a.deptid as 部门
,count(*) as 总台数
,sum(decode(OilType,0,1,0)) as 台数0
,sum(decode(OilType,0,CarMile,0)) as 里程0
,sum(decode(OilType,0,OilFare,0)) as 用油0
,sum(decode(OilType,0,OilFare*100/CarMile,0)) as 百公里0
,sum(decode(OilType,1,1,0)) as 台数1
,sum(decode(OilType,1,CarMile,0)) as 里程1
,sum(decode(OilType,1,OilFare,0)) as 用油1
,sum(decode(OilType,1,OilFare*100/CarMile,0)) as 百公里1
from CarAccount a
,CarOil b
,CarMile c
where a.carid=b.carid(+)
and a.carid=c.carid(+)
group by a.deptid
没试，你看看能用不！
[解决办法]
没有数据，他给的sql主表是CarAccount
其他两个表如果相关字段没有对应的值，是不会计算在内的。
