关于几张表统计的sql求助,很复杂(我是菜鸟,呵呵),大家帮帮看看,谢谢
-----单位信息
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年),算出各局每月的总费用(维修+保险+路桥+油费+其它。上面的数据有误,仅作举例)
大家可能会看到,后面几个费用表完全一样。其实这是一个正在运行的项目,还要计算其它的东西的,我先把表全部构造出来,省的下次再做数据(先问这两个问题,另外的
问题再开贴散分问)