-----单位信息
create table V_DWXX2(
dwdm varchar(32),----单位代码
dwmc varchar(64)---单位名称
);
insert into V_DWXX2(dwdm,dwmc) values('001','深圳地税总局');
insert into V_DWXX2(dwdm,dwmc) values('002','罗湖分局');
insert into V_DWXX2(dwdm,dwmc) values('003','南山分局');
select * from V_DWXX2;
---车辆信息
create table T_CLXXB2(
cphm varchar(16),--车牌号码
dwdm varchar(16)--所属单位
);
insert into T_CLXXB2(cphm,dwdm) values('粤b001','001');
insert into T_CLXXB2(cphm,dwdm) values('粤b002','001');
insert into T_CLXXB2(cphm,dwdm) values('粤b003','001');
insert into T_CLXXB2(cphm,dwdm) values('粤b004','002');
insert into T_CLXXB2(cphm,dwdm) values('粤b005','002');
insert into T_CLXXB2(cphm,dwdm) values('粤b006','002');
insert into T_CLXXB2(cphm,dwdm) values('粤b007','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b008','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b009','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b010','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b011','003');
select * from T_CLXXB2---保险费信息
create table t_bxfyb2(
cphm varchar(16),--车牌号码
bxje number(11,2),--保险金额
bxrq Date--日期
);
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b001',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b001',100,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b002',100,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b002',100,TO_DATE('2010-04-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b003',100,TO_DATE('2010-05-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b004',100,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b005',100,TO_DATE('2010-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b006',100,TO_DATE('2010-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b007',100,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b008',100,TO_DATE('2010-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b009',100,TO_DATE('2010-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b005',100,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b006',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from t_bxfyb2---路桥费信息
create table T_LQFYB2(
cphm varchar(16),--车牌号码
fyje number(11,2),--费用金额
fyrq Date--日期
);
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b001',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b002',100,TO_DATE('2010-04-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b003',100,TO_DATE('2010-05-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b004',100,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b007',100,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b009',100,TO_DATE('2010-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from T_LQFYB2---维修费信息
create table t_wxfyb2(
cphm varchar(16),--车牌号码
fyje number(11,2),--费用金额
fyrq Date--日期
);
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b001',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b002',100,TO_DATE('2010-04-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b003',100,TO_DATE('2010-05-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b004',100,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b007',100,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b009',100,TO_DATE('2010-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from t_wxfyb2---其它费用信息
create table t_qtfyb2(
cphm varchar(16),--车牌号码
fyje number(11,2),--费用金额
fyrq Date--日期
);
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b001',101,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b005',102,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b008',103,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b002',104,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b003',105,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b004',106,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b005',107,TO_DATE('2009-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b006',108,TO_DATE('2009-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b007',109,TO_DATE('2008-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b008',110,TO_DATE('2008-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b007',120,TO_DATE('2007-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b005',130,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b006',140,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from t_qtfyb2希望取得这样的一个查询结果1:
单位 2005 2006 2007 2008 2009 2010
深圳地税总局 0 0 100 200 300 400
罗湖分局 0 0 100 200 300 400
南山分局 0 0 100 200 300 400
根据输入的时间段(上述的是选择2006年到2010年),算出各局每年的总费用(维修+保险+路桥+油费+其它。上面的数据有误,仅作举例)希望取得这样的一个查询结果2:
单位 2010-01 2010-02 2010-03 2010-04 2010-05 2010-06
深圳地税总局 0 0 100 200 300 400
罗湖分局 0 0 100 200 300 400
南山分局 0 0 100 200 300 400以年分析,当选择一年时(如选2010年),算出各局每月的总费用(维修+保险+路桥+油费+其它。上面的数据有误,仅作举例)
大家可能会看到,后面几个费用表完全一样。其实这是一个正在运行的项目,还要计算其它的东西的,我先把表全部构造出来,省的下次再做数据(先问这两个问题,另外的问题再开贴散分问)
create table V_DWXX2(
dwdm varchar(32),----单位代码
dwmc varchar(64)---单位名称
);
insert into V_DWXX2(dwdm,dwmc) values('001','深圳地税总局');
insert into V_DWXX2(dwdm,dwmc) values('002','罗湖分局');
insert into V_DWXX2(dwdm,dwmc) values('003','南山分局');
select * from V_DWXX2;
---车辆信息
create table T_CLXXB2(
cphm varchar(16),--车牌号码
dwdm varchar(16)--所属单位
);
insert into T_CLXXB2(cphm,dwdm) values('粤b001','001');
insert into T_CLXXB2(cphm,dwdm) values('粤b002','001');
insert into T_CLXXB2(cphm,dwdm) values('粤b003','001');
insert into T_CLXXB2(cphm,dwdm) values('粤b004','002');
insert into T_CLXXB2(cphm,dwdm) values('粤b005','002');
insert into T_CLXXB2(cphm,dwdm) values('粤b006','002');
insert into T_CLXXB2(cphm,dwdm) values('粤b007','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b008','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b009','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b010','003');
insert into T_CLXXB2(cphm,dwdm) values('粤b011','003');
select * from T_CLXXB2---保险费信息
create table t_bxfyb2(
cphm varchar(16),--车牌号码
bxje number(11,2),--保险金额
bxrq Date--日期
);
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b001',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b001',100,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b002',100,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b002',100,TO_DATE('2010-04-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b003',100,TO_DATE('2010-05-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b004',100,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b005',100,TO_DATE('2010-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b006',100,TO_DATE('2010-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b007',100,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b008',100,TO_DATE('2010-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b009',100,TO_DATE('2010-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b005',100,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_bxfyb2(cphm,bxje,bxrq) values('粤b006',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from t_bxfyb2---路桥费信息
create table T_LQFYB2(
cphm varchar(16),--车牌号码
fyje number(11,2),--费用金额
fyrq Date--日期
);
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b001',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b002',100,TO_DATE('2010-04-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b003',100,TO_DATE('2010-05-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b004',100,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b007',100,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b009',100,TO_DATE('2010-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into T_LQFYB2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from T_LQFYB2---维修费信息
create table t_wxfyb2(
cphm varchar(16),--车牌号码
fyje number(11,2),--费用金额
fyrq Date--日期
);
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b001',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b002',100,TO_DATE('2010-04-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b003',100,TO_DATE('2010-05-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b004',100,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b007',100,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b008',100,TO_DATE('2010-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b009',100,TO_DATE('2010-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b005',100,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_wxfyb2(cphm,fyje,fyrq) values('粤b006',100,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from t_wxfyb2---其它费用信息
create table t_qtfyb2(
cphm varchar(16),--车牌号码
fyje number(11,2),--费用金额
fyrq Date--日期
);
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b001',101,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b005',102,TO_DATE('2010-02-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b008',103,TO_DATE('2010-03-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b002',104,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b003',105,TO_DATE('2010-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b004',106,TO_DATE('2010-06-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b005',107,TO_DATE('2009-07-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b006',108,TO_DATE('2009-08-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b007',109,TO_DATE('2008-09-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b008',110,TO_DATE('2008-10-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b007',120,TO_DATE('2007-11-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b005',130,TO_DATE('2010-12-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
insert into t_qtfyb2(cphm,fyje,fyrq) values('粤b006',140,TO_DATE('2010-01-03 12:08:26','YYYY-MM-DD HH:MI:SS'));
select * from t_qtfyb2希望取得这样的一个查询结果1:
单位 2005 2006 2007 2008 2009 2010
深圳地税总局 0 0 100 200 300 400
罗湖分局 0 0 100 200 300 400
南山分局 0 0 100 200 300 400
根据输入的时间段(上述的是选择2006年到2010年),算出各局每年的总费用(维修+保险+路桥+油费+其它。上面的数据有误,仅作举例)希望取得这样的一个查询结果2:
单位 2010-01 2010-02 2010-03 2010-04 2010-05 2010-06
深圳地税总局 0 0 100 200 300 400
罗湖分局 0 0 100 200 300 400
南山分局 0 0 100 200 300 400以年分析,当选择一年时(如选2010年),算出各局每月的总费用(维修+保险+路桥+油费+其它。上面的数据有误,仅作举例)
大家可能会看到,后面几个费用表完全一样。其实这是一个正在运行的项目,还要计算其它的东西的,我先把表全部构造出来,省的下次再做数据(先问这两个问题,另外的问题再开贴散分问)
月的话,trunc(bxrq,'mm')就可以了
select a.dwmc, to_char(c.fyrq, 'yyyy') as nd ,sum(c.bxje) as je
from V_dwxx2 a,
t_clxxb2 b,
(select cphm as cphm, bxje as bxje, bxrq as fyrq
from t_bxfyb2
union all
select cphm as cphm, fyje as bxje, fyrq as fyrq
from t_lqfyb2
union all
select cphm as cphm, fyje as bxje, fyrq as fyrq
from t_qtfyb2
union all
select cphm as cphm, fyje as bxje, fyrq as fyrq from t_wxfyb2) c
where a.dwdm = b.dwdm
and b.cphm = c.cphm
and to_char(c.fyrq, 'yyyy') between 2009 and 2010--这里输入你要控制的年限
group by a.dwmc,to_char(c.fyrq,'yyyy'))
select dwmc ,
max(case nd when '2009' then je else 0 end) as nd2009,
max(case nd when '2010' then je else 0 end) as nd2010
from t
group by dwmc
那个年度最好动态拼接
yearORmonth varchar2(6);
startYear number;
endYear number;
sqlStr varchar2(3000);
subsqlStr varchar2(2000);
begin
startYear := 2006;
endYear := 2010; if startYear = endYear then
yearORmonth := 'yyyymm';
for i in 1 .. 12 loop
subsqlStr := subsqlStr || ', max(case nd when ''' || startYear ||
lpad(i, 2, 0) || ''' then je else 0 end) as nd' ||
startYear || lpad(i, 2, 0);
end loop;
else
yearORmonth := 'yyyy';
for i in startYear .. endYear loop
subsqlStr := subsqlStr || ', max(case nd when ''' || i ||
''' then je else 0 end) as nd' || i;
end loop;
end if; sqlStr := 'with t as (
select a.dwmc, to_char(c.fyrq, ''' || yearORmonth ||
''') as nd ,sum(c.bxje) as je
from V_dwxx2 a,
t_clxxb2 b,
(select cphm as cphm, bxje as bxje, bxrq as fyrq
from t_bxfyb2
union all
select cphm as cphm, fyje as bxje, fyrq as fyrq
from t_lqfyb2
union all
select cphm as cphm, fyje as bxje, fyrq as fyrq
from t_qtfyb2
union all
select cphm as cphm, fyje as bxje, fyrq as fyrq from t_wxfyb2) c
where a.dwdm = b.dwdm
and b.cphm = c.cphm
and to_char(c.fyrq, '' yyyy '') between ' || startYear ||
' and ' || endYear || ' group by a.dwmc,to_char(c.fyrq,''' ||
yearORmonth || '''))
select dwmc' || subsqlStr || ' from t
group by dwmc';
dbms_output.put_line(sqlStr);
end;
这是动态生成sql的语句 然后你自己在提取结果集
困扰了我许久的问题终于解决了。
再请问一下:
同样的表,查询各单位平均每辆车费用的费用应该怎么写呢。