表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/里程请哪位达人.给小弟指点一二.感激不尽!!!
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
没试,你看看能用不!
不对.别的还没有看.
xxhsjp() ( ) 在吗?麻烦你再看看
CREATE TABLE CARACCOUNT
(
CAR_ID NVARCHAR2(20) NOT NULL,
CAR_CODE NVARCHAR2(50),
CAR_ORIGINMILE FLOAT(10),
CAR_SITNUM NUMBER(3),
CAR_INFO VARCHAR2(400 BYTE),
CAR_DRIVERCODE VARCHAR2(50 BYTE),
CAR_STATE NUMBER(1) DEFAULT 2,
FLOWID NUMBER(10) NOT NULL,
CARBOOK_NUM NUMBER(4) DEFAULT 0,
DEP_CODE VARCHAR2(10 BYTE),
OILMIN NUMBER(6,2),
OILMAX NUMBER(6,2),
PICTURE_GROUPID NUMBER(10),
CAR_TYPE VARCHAR2(50 BYTE),
DEP_CODE_MANAGE VARCHAR2(10 BYTE),
OILTYPE NUMBER(1),
ISCOMPUTEMILE NUMBER(1),
RATIONTYPE NUMBER(10)
)
TABLESPACE MME
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
NOMONITORING;COMMENT ON COLUMN CARACCOUNT.CAR_ID IS 'ID';COMMENT ON COLUMN CARACCOUNT.CAR_CODE IS '车牌号';COMMENT ON COLUMN CARACCOUNT.OILMIN IS '百公里最小定额';COMMENT ON COLUMN CARACCOUNT.OILMAX IS '百公里最大定额';COMMENT ON COLUMN CARACCOUNT.PICTURE_GROUPID IS '车辆图片';COMMENT ON COLUMN CARACCOUNT.CAR_ORIGINMILE IS '初始公里数';COMMENT ON COLUMN CARACCOUNT.CAR_TYPE IS '车辆类型';COMMENT ON COLUMN CARACCOUNT.DEP_CODE_MANAGE IS '所属部门';COMMENT ON COLUMN CARACCOUNT.CAR_SITNUM IS '座位数';COMMENT ON COLUMN CARACCOUNT.CAR_INFO IS '备注';COMMENT ON COLUMN CARACCOUNT.CAR_DRIVERCODE IS '驾驶员驾照证号';COMMENT ON COLUMN CARACCOUNT.OILTYPE IS '加油类型(0:汽油/1:柴油)';COMMENT ON COLUMN CARACCOUNT.CAR_STATE IS '0:Repair; 1:Outing; 2:Backed; 3:Booking; 4:Repair&Booking; 5:Outing&Booking';COMMENT ON COLUMN CARACCOUNT.ISCOMPUTEMILE IS '是否计算里程(0:不计算/1:计算)';COMMENT ON COLUMN CARACCOUNT.FLOWID IS '流水号';COMMENT ON COLUMN CARACCOUNT.RATIONTYPE IS '燃油考核定额表ID';-----------------------------------------------------------
CREATE TABLE ADDOIL
(
ADDOIL_ID NUMBER(10) NOT NULL,
ADDOIL_DATE DATE NOT NULL,
OIL_NUM NUMBER(6,2) NOT NULL,
OIL_CHARGE NUMBER(6,2) NOT NULL,
USECARD CHAR(1 BYTE),
OIL_STATION VARCHAR2(200 BYTE),
INFO VARCHAR2(400 BYTE),
CAROUT_ID NUMBER(10),
CARID NVARCHAR2(20) NOT NULL,
ADDOIL_DRIVER NUMBER(10),
OILTYPE_CODE VARCHAR2(10 BYTE)
)
TABLESPACE MME
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
NOMONITORING;
------------------------------------------
CREATE TABLE MONTHRUNMILE
(
CAR_ID NVARCHAR2(20) NOT NULL,
DRIVERCODE VARCHAR2(50 BYTE),
REGISTDATE DATE NOT NULL,
MONTHMILE FLOAT(10) NOT NULL,
INFO VARCHAR2(400 BYTE),
FLOW_ID NUMBER(10) NOT NULL,
UPMONTH_RUNMILE NUMBER(9,1)
)
TABLESPACE MME
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
NOMONITORING;
其他两个表如果相关字段没有对应的值,是不会计算在内的。